I have a simple table ->
id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY
by_id INT UNSIGNED NOT NULL
posted_on INT UNSIGNED NOT NULL
My table engine is MyISAM.
I have a multiple column index called combo1 on by_id,posted_on,id
I run this query ->
EXPLAIN SELECT * FROM books
WHERE by_id = '1' AND posted_on = '0'
ORDER BY id DESC LIMIT 7;
The Extra column says Using where and key column says combo1
But, when i run this query ->
EXPLAIN SELECT * FROM books
WHERE by_id IN(1,7,10) AND posted_on = '0'
ORDER BY id DESC LIMIT 7;
The Extra column says Using where; Using filesort and key column says combo1.
Why is a filesort occuring in second case even though the QEP shows that optimizer is using the index combo1 which has 'id' indexed in it.
The index is a B+ tree. That means that under the by_id 1 there are all records with posted_on 0 and by_id 1, and then you have all the ids for those records. Under the by_id 7 however you have another tree branch, that contains records with posted_on 0 and they contains the records with their ids.
When you have in clause, you are retrieving 3 different branches of the tree, you have to merge them and resort them, since ids with 1,2,4 may be under by_id 1, but 3,5 under by_id 10; MySQL retrieves 1,2,4,3,5 and have to resort them.
In the first case there is only one branch, and each branch is already sorted