Using IN() clause resulting in Filesort

Question

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.

Answer 1

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