How can I solve "Using filesort" for this SQL?

Question

There is the table:

CREATE TABLE `deal_keyword` (
  `deal_id` int(11) unsigned NOT NULL default '0',
  `keyword_id` int(11) unsigned NOT NULL default '0',
  `area_id` int(11) unsigned NOT NULL default '0',
  PRIMARY KEY  (`deal_id`,`keyword_id`),
  KEY `area_id` (`area_id`,`keyword_id`,`deal_id`)
) TYPE=MyISAM;

And input some records for test:

insert into deal_keyword values(1,2,3);
insert into deal_keyword values(2,2,3);
insert into deal_keyword values(3,2,3);
insert into deal_keyword values(4,1,3);
insert into deal_keyword values(5,1,3);
insert into deal_keyword values(6,1,3);
insert into deal_keyword values(7,3,3);
insert into deal_keyword values(8,3,3);

When I run this SQL:

explain select * from `deal_keyword` where `area_id`=3 && `keyword_id` in(1,3) order by `deal_id`;

+--------------+-------+---------------+---------+---------+------+------+------------------------------------------+
| table        | type  | possible_keys | key     | key_len | ref  | rows | Extra                                    |
+--------------+-------+---------------+---------+---------+------+------+------------------------------------------+
| deal_keyword | range | area_id       | area_id |       8 | NULL |    4 | Using where; Using index; Using filesort |
+--------------+-------+---------------+---------+---------+------+------+------------------------------------------+

How can I solve "Using filesort" for this SQL?

Thanks for help :)

Answer 1

Create a composite index on (area_id, deal_id, keyword_id) (in this order)

Note that if you had additional columns in you table, index scan would not necessarily be better than a filesort for an ORDER BY without a LIMIT, since traversing the index requires table lookups which imply random seeks and are not efficient.