Tell me more ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.
up vote 0 down vote favorite

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     | <derived2>      | 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.

share|improve this question
2  
your question was already migrated here dba.stackexchange.com/questions/36708/… – Phil Mar 14 at 16:59
Can you try adding an index on (expiration, uid) ? – ypercube Mar 15 at 7:01

2 Answers

up vote 0 down vote accepted

I don't have time to check; however, just looking at the query I expect the filesort is being triggered by the ORDER BY/LIMIT. There isn't much use in doing a sequential O(n) pass over the uid index when it will still have to do O(n) datafile lookups followed by an external sort to extract the first 12 before performing the 12 (or-so) eq_ref joins against usertable's id index followed by probes into usertable's datafile to extract the fields required by select.

I expect Mysql believes (correctly) that it is better to do a single sequential O(n) pass over the datafile followed by the external sort, the eq_ref joins and the probes.

What you need is to do the external sort for mysql up-front, so it doesn't have to do it itself. That means you need a covering index over the join/where conditions.

ie.

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

Then again, given there are only 1020 rows in online, why are you worrying about this now?

share|improve this answer
Recurse, I accept your answer. First of all I deleted column "id" because it's not used. Then I added ondex on (expiration, uid). That solved the problem. – lpalpa Mar 15 at 8:51
up vote 3 down vote

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

share|improve this answer
I updated my post with "explain". It still uses filesort. – lpalpa Mar 14 at 16:50
I update my answer with another refactored query and an index adjustment. – RolandoMySQLDBA Mar 14 at 16:55
@lpalpa "still uses filesort" may be or may not be an issue. Is it faster? – ypercube Mar 15 at 7:04

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.