How to avoid a filesort on a mysql composite index

我有三个colums（德国的双字母组），1 GB的MySQL表：

create table sortedindex (source varchar(60),target varchar(60),score float)
engine=myisam character set utf8 collate utf8_bin;

我还创建了一个复合索引：

create index sortedstd_ix on sortedindex (source(60), target(60), score);

另外我压缩表，并使其只读，并使用排序的指标：

myisamchk --keys-used=0 -rq sortedindex
myisampack sortedindex
myisamchk -rq sortedindex --sort_buffer=3G --sort-index --sort-records=1

现在我想问结构如下疑问：

• 固定的源极
• 指定目标的前缀
• 由得分最高检索k行

像下面这样：

select * from sortedindex where source like "ein" and target like "interess%" order by score desc limit 5;

MySQL的解释告诉我，依然采用了文件排序！

mysql> explain select * from sortedindex where source like "ein" and target like "interess%" order by score desc limit 5;
+----+-------------+-------------+-------+---------------+--------------+---------+------+------+------------------------------------------+
| id | select_type | table       | type  | possible_keys | key          | key_len | ref  | rows | Extra                                    |
+----+-------------+-------------+-------+---------------+--------------+---------+------+------+------------------------------------------+
|  1 | SIMPLE      | sortedindex | range | sortedstd_ix  | sortedstd_ix | 366     | NULL |   17 | Using where; Using index; Using filesort |
+----+-------------+-------------+-------+---------------+--------------+---------+------+------+------------------------------------------+
1 row in set (0.00 sec)`

我明白，如果我更改查询到：

explain select * from sortedindex where source like "ein" and target like "interess%" order by source, target, score desc limit 5;

不会有任何文件排序，但错在那里是涉及到文件排序。

mysql> explain select * from sortedindex where source like "ein" and target like "interess%" order by source, target, score desc limit 5;
+----+-------------+-------------+-------+---------------+--------------+---------+------+------+------------------------------------------+
| id | select_type | table       | type  | possible_keys | key          | key_len | ref  | rows | Extra                                    |
+----+-------------+-------------+-------+---------------+--------------+---------+------+------+------------------------------------------+
|  1 | SIMPLE      | sortedindex | range | sortedstd_ix  | sortedstd_ix | 366     | NULL |   17 | Using where; Using index; Using filesort |
+----+-------------+-------------+-------+---------------+--------------+---------+------+------+------------------------------------------+
1 row in set (0.00 sec)

从这个讨论我意识到DESC关键字的问题。 因此，我们没有检查：

mysql> explain select * from sortedindex where source like "ein" and target like "interess%" order by source, target, score limit 5;
+----+-------------+-------------+-------+---------------+--------------+---------+------+------+--------------------------+
| id | select_type | table       | type  | possible_keys | key          | key_len | ref  | rows | Extra                    |
+----+-------------+-------------+-------+---------------+--------------+---------+------+------+--------------------------+
|  1 | SIMPLE      | sortedindex | range | sortedstd_ix  | sortedstd_ix | 366     | NULL |   17 | Using where; Using index |
+----+-------------+-------------+-------+---------------+--------------+---------+------+------+--------------------------+
1 row in set (0.00 sec)

完善奏效。

但我想一个下降的得分排序，而不是目标。 用这种方法创建索引

create index sortedstd_ix on sortedindex (source(60), score desc, target(60));

是不是一种选择，因为目标过滤器将产生一个文件，然后排序，或者如果不是需要遍历可真够长的，如果前缀是长源是一种常见的字元素的结果列表。

不知何故，我有一种感觉，没有明显的解决方案呢？

Answer 1:

你是对的。 还有就是这个没有明显的解决方案。 需要排序，因为你所要求的目标的多个值（如“interess％”）。 因此，指数不会给你那是在得分排序列。

Answer 2:

尝试这个 ：：

select * from sortedindex 
where source like "ein" and target like "interess%" 
order by score desc, source, target limit 5;

文章来源: How to avoid a filesort on a mysql composite index for a prefix query with an order by statement?