MySQL: can not get rid of “Using filesort” in a simple query Simple query but can not get rid of "using filesort":

CREATE TABLE IF NOT EXISTS `online` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `uid` int(11) NOT NULL,
  `expiration` int(11) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `uid` (`uid`),
  KEY `expiration` (`expiration`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=1925234 ;

mysql&gt; explain SELECT i.id, i.uid, i.expiration, u.nick, u.mainpicture
    -&gt; FROM online i join usertable u on i.uid = u.id
    -&gt; order by i.expiration DESC limit 0,12;

+----+-------------+-------+--------+---------------+---------+---------+----------------+------+----------------+
| id | select_type | table | type   | possible_keys | key     | key_len | ref            | rows | Extra          |
+----+-------------+-------+--------+---------------+---------+---------+----------------+------+----------------+
|  1 | SIMPLE      | i     | ALL    | uid           | NULL    | NULL    | NULL           | 1020 | Using filesort |
|  1 | SIMPLE      | u     | eq_ref | PRIMARY       | PRIMARY | 4       | dbasen01.i.uid |    1 |                |
+----+-------------+-------+--------+---------------+---------+---------+----------------+------+----------------+
2 rows in set (0.00 sec)


"usertable" has index on "id" column. Tried to order by "i.id" (primary key) but the same - query still uses filesort.

Update after RolandoMySQLDBA answer: 

+----+-------------+-----------------+--------+---------------+---------+---------+-------+------+----------------+
| id | select_type | table           | type   | possible_keys | key     | key_len | ref   | rows | Extra          |
+----+-------------+-----------------+--------+---------------+---------+---------+-------+------+----------------+
|  1 | PRIMARY     | &lt;derived2&gt;      | ALL    | NULL          | NULL    | NULL    | NULL  |   12 |                |
|  1 | PRIMARY     | u               | eq_ref | PRIMARY       | PRIMARY | 4       | i.uid |    1 |                |
|  2 | DERIVED     | online          | ALL    | NULL          | NULL    | NULL    | NULL  | 1009 | Using filesort |
+----+-------------+-----------------+--------+---------------+---------+---------+-------+------+----------------+


Solution:

I deleted column "id" because it's not used. Then modified index:

ALTER TABLE online ADD INDEX expiration (expiration,uid);


Result:

+----+-------------+-------+--------+---------------+------------+---------+----------------+------+-------------+
| id | select_type | table | type   | possible_keys | key        | key_len | ref            | rows | Extra       |
+----+-------------+-------+--------+---------------+------------+---------+----------------+------+-------------+
|  1 | SIMPLE      | i     | index  | uid           | expiration | 8       | NULL           |   12 | Using index |
|  1 | SIMPLE      | u     | eq_ref | PRIMARY       | PRIMARY    | 4       | dbasen01.i.uid |    1 |             |
+----+-------------+-------+--------+---------------+------------+---------+----------------+------+-------------+


Thanks to all.
Here is your original query

SELECT i.id, i.uid, i.expiration, u.nick, u.mainpicture
FROM online i join usertable u on i.uid = u.id
order by i.expiration DESC limit 0,12;


Try refactoring the query to coerce the retrieval of 12 rows before doing the JOIN

SELECT i.id, i.uid, i.expiration, u.nick, u.mainpicture FROM 
(SELECT * FROM online ORDER BY expiration DESC limit 0,12) i
LEFT JOIN usertable u on i.uid = u.id;


The subquery may be forced to use the expiration index now.

You can also try this one:

SELECT i.id, i.uid, i.expiration, u.nick, u.mainpicture FROM 
(SELECT id FROM online ORDER BY expiration DESC limit 0,12) ik
LEFT JOIN online i on ik.uid = u.id;
LEFT JOIN usertable u on ik.uid = u.id;


but you will need redo the expiration index

ALTER TABLE online DROP INDEX expiration;
ALTER TABLE online ADD INDEX expiration (expiration,id);


Give it a Try !!!