creating index is not always good

MySQL & MariaDB Other MySQL® Questions
1

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.

2

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?

3

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