SQL Query always uses filesort in order by clause

Question

Hello all

I am trying to optimize a sql query which is using order by clause. When I use EXPLAIN the query always displays "using filesort". I am applying this query for a group discussion forum where there are tags attached to posts by users.

Here are the 3 tables I am using: users, user_tag, tags

user_tag is the association mapping table for users and their tags.

CREATE TABLE `usertable` (
 `user_id` int(11) unsigned NOT NULL AUTO_INCREMENT,
 `user_name` varchar(20) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,
 PRIMARY KEY (`user_name`),
 KEY `user_id` (`user_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

CREATE TABLE `user_tag` (
 `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
 `user_id` int(11) unsigned NOT NULL,
 `tag_id` int(11) unsigned NOT NULL,
 `usage_count` int(11) unsigned NOT NULL,
 PRIMARY KEY (`id`),
 KEY `tag_id` (`tag_id`),
 KEY `usage_count` (`usage_count`),
 KEY `user_id` (`user_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

I update the usage_count on server side using programming. Here is the query that's giving me problem. The query is to find out the tag_id and usage_count for a particular username, sorted by usage count in descending order

select user_tag.tag_id, user_tag.usage_count
  from user_tag inner join usertable on usertable.user_id = user_tag.user_id
 where user_name="abc" order by usage_count DESC;

Here is the explain output:

mysql> explain select
    user_tag.tag_id,
    user_tag.usage_count from user_tag
    inner join usertable on
    user_tag.user_id = usertable.user_id
    where user_name="abc" order by
    user_tag.usage_count desc;

Explain output here

What should I be changing to lose that "Using filesort"

Answer 1

I'm rather rusty with this, but here goes.

The key used to fetch the rows is not the same as the one used in the ORDER BY:

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

As mentioned by OMG Ponies, an index on user_id, usage_count may resolve the filesort.

KEY `user_id_usage_count` (`user_id`,`usage_count`)

Answer 2

"Using filesort" is not necessarily bad; in many cases it doesn't actually matter.

Also, its name is somewhat confusing. The filesort() function does not necessarily use temporary files to perform the sort. For small data sets, the data are sorted in memory which is pretty fast.

Unless you think it's a specific problem (for example, after profiling your application on production-grade hardware in the lab, removing the ORDER BY solves a specific performance issue), or your data set is large, you should probably not worry about it.