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> explain 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;

+----+-------------+-------+--------+---------------+---------+---------+----------------+------+----------------+
| 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 | | ALL | NULL | NULL | NULL | NULL | 12 | |
| 1 | PRIMARY | u | eq_ref | PRIMARY | PRIMARY | 4 | i.uid | 1 | |
| 2 | DERIVED | online

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 !!!

StackExchange Database Administrators Q#36705, answer score: 3