MySQL Performance: MySQL => creating index is not always good

Members

Help

Home

Home » Performance » MySQL » creating index is not always good

Show: Today's Messages :: Show Polls :: Message Navigator

creating index is not always good [message #3401]

Sun, 10 August 2008 09:56

tera7
Messages: 2
Registered: August 2008

Junior Member

Hello i have a db with 2 million rows my id are index so i issue the following :

SELECT * FROM table WHERE cat=3 ORDER BY id DESC LIMIT 100,50;

50 rows in set (0.00 sec)

mysql> explain SELECT * FROM table WHERE cat=3 ORDER BY id DESC LIMIT 100,50;
+----+-------------+----------+-------+---------------+----- -+---------+------+-
--------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref |
rows | Extra |
+----+-------------+----------+-------+---------------+----- -+---------+------+-
--------+-------------+
| 1 | SIMPLE | table | index | NULL | id | 4 | NULL |
2818801 | Using where |
+----+-------------+----------+-------+---------------+----- -+---------+------+-
--------+-------------+
1 row in set (0.32 sec)

this is very fast.

If i make the cat field index it takes 12 seconds.At explain it uses as key the cat field reducing the rows scanned to 400000 aprx. but using filesort which is explode the time needed to 12 seconds.

So based to my tests using indexes may not having your desired results.

Also i have another website in which indexes make a big diferrence but i think that mysql must be optimized per case there is not universal standarts.

i am sorry for my bad english.

Report message to a moderator

Re: creating index is not always good [message #3476 is a reply to message #3401 ]

Fri, 29 August 2008 03:53

chrisbolt
Messages: 1
Registered: August 2008

Junior Member

Before you added the cat index it could use the id index for the ORDER BY. If you created a composite index on (cat, id) it would be just as fast, if not faster.

And if your query is already running in 0.00 seconds, why are you adding an index?

Report message to a moderator

Re: creating index is not always good [message #3477 is a reply to message #3476 ]

Fri, 29 August 2008 11:20

tera7
Messages: 2
Registered: August 2008

Junior Member

Yes my men i had a composite index and it was very slow cause used the id for filesort and that did a lot of time.My thoughts is that sorting is faster to be done in indexed id's using them as keys.In my example with composite indexes just takes the cat key for where and then makes filesort to id's which is the slow action.
I did it just for testing to compare my databases cause other databases i have are faster with composite indexes ,but that is happening cause is more complex the data handling through php, so we make all queries little bit slower, but none of them slow enough to hurt the speed of my site(cause i have seen that a slow query can push all queries to be slower than they really are).
Thanks

Report message to a moderator

Previous Topic:	Innodb and Partitioning
Next Topic:	replication of load data infile

Goto Forum:

-=] Back to Top [=-

[ Generate printable PDF ] [ Syndicate this forum (XML) ] [

]

Current Time: Fri Dec 5 11:27:58 EST 2008

Total time taken to generate the page: 0.01216 seconds

.:: Contact :: Home :: MySQL Support by Percona.com ::.