SQL查询总是按子句顺序使用filesort

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.

我正在尝试优化一个使用order by子句的sql查询。当我使用EXPLAIN时，查询总是显示“使用filesort”。我正在为一个群组讨论论坛应用这个查询，在这个论坛中有用户贴在帖子上的标签。

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

这里是我使用的3个表:用户、user_tag、标签。

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

user_tag是用户及其标记的关联映射表。

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

我使用编程在服务器端更新usage_count。这是给我带来问题的查询。查询将查找特定用户名的tag_id和usage_count，并按使用计数降序排序

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"

如果我失去了"使用文件排序"

2 个解决方案

#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:

用于获取行的键与ORDER中使用的键不相同:

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.

正如OMG Ponies所提到的，user_id上的一个索引，usage_count可以解析文件。

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

#2

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

“使用filesort”并不一定是坏事;在很多情况下，这并不重要。

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.

而且，它的名字有点让人困惑。filesort()函数不一定要使用临时文件来执行排序。对于小数据集，数据在内存中排序，速度非常快。

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.

除非您认为这是一个特定的问题(例如，在实验室中分析生产级硬件上的应用程序，通过解决特定的性能问题来删除订单)，或者您的数据集很大，否则您可能不应该为此担心。

#1