MySQL stuck on "using filesort" when doing an "order by"

Question

I can't seem to get my query to stop using filesort.

This is my query:

SELECT s.`pilot`, p.`name`, s.`sector`, s.`hull` 
FROM `pilots` p 
 LEFT JOIN `ships` s ON ( (s.`game` = p.`game`) 
  AND (s.`pilot` = p.`id`) ) 
WHERE p.`game` = 1 
 AND p.`id` <> 2 
 AND s.`sector` = 43 
 AND s.`hull` > 0 
ORDER BY p.`last_move` DESC

Table structures:

CREATE TABLE IF NOT EXISTS `pilots` (
  `id` mediumint(5) unsigned NOT NULL AUTO_INCREMENT,
  `game` tinyint(3) unsigned NOT NULL DEFAULT '0',
  `last_move` int(10) NOT NULL DEFAULT '0',
  UNIQUE KEY `id` (`id`),
  KEY `last_move` (`last_move`),
  KEY `game_id_lastmove` (`game`,`id`,`last_move`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=8 ;

CREATE TABLE IF NOT EXISTS `ships` (
  `id` mediumint(5) unsigned NOT NULL AUTO_INCREMENT,
  `game` tinyint(3) unsigned NOT NULL DEFAULT '0',
  `pilot` mediumint(5) unsigned NOT NULL DEFAULT '0',
  `sector` smallint(5) unsigned NOT NULL DEFAULT '0',
  `hull` smallint(4) unsigned NOT NULL DEFAULT '50',
  UNIQUE KEY `id` (`id`),
  KEY `game` (`game`),
  KEY `pilot` (`pilot`),
  KEY `sector` (`sector`),
  KEY `hull` (`hull`),
  KEY `game_2` (`game`,`pilot`,`sector`,`hull`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=8 ;

The explain:

id  select_type  table  type  possible_keys  key  key_len  ref  rows  Extra
1  SIMPLE     p  ref  id,game_id_lastmove  game_id_lastmove  1  const  7  Using where; Using filesort
1  SIMPLE     s  ref  game,pilot,sector...  game_2  6  const,fightclub_alpha.p.id,const  1    Using where; Using index

edit: I cut some of the unnecessary pieces out of my queries/table structure.

Anybody have any ideas?

Answer 1

the best thing that you can do is to make indexes:

1. index that covers table ships with fields: game + pilot + sector + hull (in this specific order)
2. pilots: game + id

this particular query will always use filesort, because it has not range condition p.id <> 2

Answer 2

http://dev.mysql.com/doc/refman/5.0/en/order-by-optimization.html

In some cases, MySQL cannot use indexes to resolve the ORDER BY, although it still uses indexes to find the rows that match the WHERE clause. These cases include the following ... The key used to fetch the rows is not the same as the one used in the ORDER BY