Question about table indexes

performance

ez	#1 2010-02-11 21:29
Contributors Thanked: 10 times	Hi, Can anyone tell me why in the pages table the page_begin and page_expire are not indexed ? For example the newsplugin uses these fields in the select statements, so maybe it is better to have them indexed ? Also page_ownerid wich is used to look up avatars. I hope that anybody can tell me. I want to improve the speed (It is allready fast, but there is room for improvement) greetz, Leo (ez) ==- I say: Keep it EZ -==

#1 2010-02-11 21:29

Hi,

Can anyone tell me why in the pages table the page_begin and page_expire are not indexed ?

For example the newsplugin uses these fields in the select statements, so maybe it is better to have them indexed ? Also page_ownerid wich is used to look up avatars.

I hope that anybody can tell me.
I want to improve the speed (It is allready fast, but there is room for improvement)

greetz,

Leo (ez)

==- I say: Keep it EZ -==

Trustmaster	#2 2010-02-12 04:05
Administrators Thanked: 263 times	Having too many indexes may have a downside effect, that's why we don't add indexes to every single column. May the Source be with you!

ez	#3 2010-02-12 04:37
Contributors Thanked: 10 times	Hi Trustmaster, I know this already... I have done some SQL optimizing myself (NOT MySql). But I was wondering howto best tweak database performance (MySQL) And pages are used a lot, so this is a good place to look But it is hard when loading times vary so much.. (I am looking on a website on a shared server) page3 is interesting!! http://www.databasejournal.com/features/mysql/article.php/10897_1382791_3/Optimizing-MySQL-Queries-and-Indexes.htm ==- I say: Keep it EZ -==
This post was edited by ez (2010-02-12 05:01, 14 years ago)

#3 2010-02-12 04:37

Contributors
Thanked: 10 times

Hi Trustmaster,

I know this already... I have done some SQL optimizing myself (NOT MySql).

But I was wondering howto best tweak database performance (MySQL)

And pages are used a lot, so this is a good place to look

But it is hard when loading times vary so much.. (I am looking on a website on a shared server)

page3 is interesting!!

http://www.databasejournal.com/features/mysql/article.php/10897_1382791_3/Optimizing-MySQL-Queries-and-Indexes.htm

==- I say: Keep it EZ -==

This post was edited by ez (2010-02-12 05:01, 14 years ago)

Trustmaster	#4 2010-02-12 14:11
Administrators Thanked: 263 times	We don't mind adding more indexes if it is reasonable. To know that there should be a kind of research, involving EXPLAIN queries, query execution times in debug mode and RPS benchmarks. I agree that we should pay more attention to it. And any help with MySQL performance is appreciated. May the Source be with you!

ez	#5 2010-02-12 16:49
Contributors Thanked: 10 times	I would gladly help... I will try to set up a test on a more stable server. Like I said.. I know some ground rules about optimizing sql, but you are right we need more research.. If anybody can help, please let us know.. ==- I say: Keep it EZ -==

Trustmaster	#6 2010-02-12 20:19
Administrators Thanked: 263 times	You need to fill the database with thousands of records to perform a test close enough to real conditions. And I recommend doing it on localhost, or your customers/hosting won't like these experiments May the Source be with you!

ez	#7 2010-02-12 20:25
Contributors Thanked: 10 times	Hosting... screw them (i am paying) Clients... Yes, i will be carefull with that I need to do testing, but some things I know in advance... Like page_state index is not good... (Thats a fact) Indexes only work if there is a lot off different data... page_state is 0 or 1 so this is not worth indexing. This is base knowledge indexing greetz ==- I say: Keep it EZ -==