Let's suppose I have the following table (let's call it my_table
):
CREATE TABLE `my_table` (
`table_id` int(10) unsigned NOT NULL auto_increment,
`my_field` int(10) unsigned NOT NULL default '0'
PRIMARY KEY (`table_id`),
KEY `my_field` (`my_field`,`table_id`)
) ENGINE=MyISAM
The primary key for my_table
is table_id
(auto_increment) and I also have a key with my_field
and table_id
.
If I test this query...
EXPLAIN SELECT * FROM my_table
WHERE my_field = 28
ORDER BY table_id DESC;
... I get:
id select_type table type possible_keys key key_len ref rows Extra --- ----------- -------- ---- ------------- -------- ------- ----- ---- ----- 1 SIMPLE my_table ref my_field my_field 8 const 36
You can see that it's using the correct key (my_field
).
But if I try this...
EXPLAIN SELECT * FROM my_table
WHERE my_field IN (1, 28, 20)
ORDER BY table_id DESC;
... I get:
id select_type table type possible_keys key key_len ref rows Extra --- ----------- -------- ---- ------------- ------ ------- ------ ---- --------------------------- 1 SIMPLE my_table ALL my_field (NULL) (NULL) (NULL) 406 Using where; Using filesort
You can see that it's not using any key at all, and worse, using filesort.
Even if I do "FORCE INDEX (my_field)
", It still does the filesort.
Is there any way to avoid the filesort?