Как избежать сортировки файлов по составному индексу mysql для запроса префикса с оператором order by?

Question

У меня есть таблица MySQL объемом 1 ГБ с тремя столбцами (немецкие биграммы):

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;