Запрос с Usung filesort работает быстрее

[_RVK_]

Запрос с Usung filesort работает быстрее

Существует мнение что если в EXPLAIN запроса есть Usung filesort, то это плохо. Но сейчас, с удивлением столкнулся со случаем когда все наоборот.

mysql> EXPLAIN SELECT album.* FROM album WHERE photos != "0" AND name LIKE "%girl%" ORDER BY modified DESC;
+----+-------------+-------+------+---------------+------+---------+------+--------+-----------------------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows   | Extra                       |
+----+-------------+-------+------+---------------+------+---------+------+--------+-----------------------------+
|  1 | SIMPLE      | album | ALL  | NULL          | NULL |    NULL | NULL | 575820 | Using where; Using filesort |
+----+-------------+-------+------+---------------+------+---------+------+--------+-----------------------------+
1 row in set (0.00 sec)

mysql> EXPLAIN SELECT album.* FROM album FORCE INDEX (modified_) WHERE photos != "0" AND name LIKE "%girl%" ORDER BY modified DESC;
+----+-------------+-------+-------+---------------+-----------+---------+------+--------+-------------+
| id | select_type | table | type  | possible_keys | key       | key_len | ref  | rows   | Extra       |
+----+-------------+-------+-------+---------------+-----------+---------+------+--------+-------------+
|  1 | SIMPLE      | album | index | NULL          | modified_ |       5 | NULL | 575820 | Using where |
+----+-------------+-------+-------+---------------+-----------+---------+------+--------+-------------+
1 row in set (0.00 sec)

Второй запрос отрабатывает на порядок медленее.

Вот индексы

mysql> show index from album;
+-------+------------+--------------------+--------------+-------------------+
| Table | Non_unique | Key_name           | Seq_in_index | Column_name       | 
+-------+------------+--------------------+--------------+-------------------+
| album |          0 | PRIMARY            |            1 | id                |
| album |          1 | category_          |            1 | category          | 
| album |          1 | password_          |            1 | password          | 
| album |          1 | created_           |            1 | created           | 
| album |          1 | modified_          |            1 | modified          | 
| album |          1 | owner_             |            1 | owner             | 
| album |          1 | owner_             |            2 | photos            | 
| album |          1 | store_             |            1 | store             | 
| album |          1 | user_id            |            1 | user_id           | 
| album |          1 | counter            |            1 | counter           | 
| album |          1 | name               |            1 | name              | 
| album |          1 | _prior_day_counter |            1 | prior_day_counter | 
| album |          1 | cat_genre          |            1 | category          | 
| album |          1 | cat_genre          |            2 | photos            | 
| album |          1 | cat_genre          |            3 | modified          | 
| album |          1 | name_              |            1 | name              | 
| album |          1 | description_       |            1 | description       | 
+-------+------------+--------------------+--------------+-------------------+
17 rows in set (0.00 sec)

Интересно, почему сабж.

 

[SunDrop]

Есть подозрение.
Выскажу мнение.

В MySQL 4.0.9 можно также указывать FORCE INDEX. Это работает также, как и USE INDEX (key_list) но в дополнение дает понять серверу что полное сканирование таблицы будет ОЧЕНЬ дорогостоящей операцией. Другими словами, в этом случае сканирование таблицы будет использовано только тогда, когда не будет найдено другого способа использовать один из данных индексов для поиска записей в таблице.

Предположение:
т.к. при LIKE "%girl%" индексы НЕ используются, то сначала идет работа с индексами, а после этого происходит все то-же, что и в первом случае полное сканирование таблицы!

 

[_RVK_]

То есть если первый запрос отрабатывает ~2 сек. а второй примерно ~20 сек. То 18 сек. mysql занимется.... вот чем она в это время занимается я не понял. Очевидно что индекс я её заставил использовать при сортировке, то есть после выборки. Вот что интересно... Она тормозит именно в момент сортировки уже выбранных данных, причем, делая это с использованием индекса, работает медленее.

 

[Popoff]

_RVK_
(условно) С использованием индексов - две операции чтения на каждую строку таблицы: 1) в индексе 2) в нужном месте - в файле данных; без использования - считывается сразу весь файл данных и просматривается по порядку.

Либо оба файла загрузить, причем ходить по файлу с данными ходить в практически случайном порядке, либо загрузить только файл с данными и просмотреть его с начала до конца.

 

[ForJest]

Отсюда простой вывод - муся - умнее

 

[MadMike]

_RVK_, а по-моему, дело происходит не так. Ты заставил его юзать индекс при выборке рядов, а не при order_by.
Происходит следующее:
Мускуль идет по всему дереву индекса modified_, пытаясь подобрать подходящие элементы. Так как условия на modified нет, то он пройдет по всему дереву. Это, может быть и не так долго, и дело, как верно подметил Popoff, в произвольном порядке доступа.
Так что с тем, что тормоза появляются при сортировке - ты не прав

 

[Wicked]

ForJest прав Я сам не использую force index, т.к. на ум приходит только один случай, когда муся не смогла построить оптимальный план. Вроде было это в случае, когда использовался индекс index1 (field1, field2), и в запросе был group by field2, field1 (т.е. те же поля, но в другом порядке, что роли не играет).

_RVK_, может стоит сделать индекс по (photos, modified) ?

 

[fisher]

rvk, using filesort _всегда_ плохо, но это совершенно не значит, что нельзя составить запрос без filesort который при определенных условиях будет ещё хуже.
почему-то мне кажется, что ты вообще не то замерил, что хотел.
может, методом научного тыка чуть поиграться с запросами - чтобы понять, из-за чего именно тупка - убрать скажем order by или один из предикатов в where или наоборот попробовать поиграть с другими запросами которые используют индекс modified... как правило при таких играх вскрываются новые особенности жизни твоей системы, которые могут опровергнуть скороспешные выводы. у тебя много индексов и похоже система очень нагружена - там вариатов может быть море. что-то не так с modified - может, его перестроить надо, может слишком много апдейтов идет, пожет памяти мало и этот индекс не в кэше... исследовать надо. сколько строк в таблице? сколько памяти на сервере? что в настройках мыскля? нет ли аномалий в show status и тд?

что касается теории - ну потому rule-based и устаревший подход, а cost-based - наоборот, mysql здесь пока сильно отстает.

 

[_RVK_]

fisher
Играйся - неиграйся а запрос изменить нельзя. Ни из where ничего нельзя убирать, ни order by менять... Конечно поиск с помощью LIKE по таблице с полумиллионом записей это неоптимально(мягко говоря), но пока ничего не поделаешь. Играть можно только индексами.

почему-то мне кажется, что ты вообще не то замерил, что хотел.

Выполнил запрос, посмотрел время которое мне сообщил клиент.

у тебя много индексов

Потому что добавляют все кому не лень добавить но лень посмотреть может индекс по полю уже есть.

похоже система очень нагружена

Ну это примерно 200 запросов в сек. По опыту система сносно переносила 1500 запросов.

может слишком много апдейтов идет

Нет там апдейтов - это слейв. Точнее конечно же есть, но не думаю что это причина.

пожет памяти мало и этот индекс не в кэше

А вот это вполне может быть, так как вся база 4 гига, и индекс большой.

Но копать так глубоко, мне никто времени не даст, хотя интересно было бы докапаться до истины. Да и понятно что точную причину мне тут никто не скажет. Слишком мало информации. Так что вопрос скорее риторический

 

[SelenIT]

Wicked

те же поля, но в другом порядке, что роли не играет

AFAIK, при составном индексе еще как играет.

 

[AnToXa]

http://dev.mysql.com/doc/refman/4.1/en/join.html

USE INDEX, IGNORE INDEX, and FORCE INDEX affect only which indexes are used when MySQL decides how to find rows in the table and how to do the join. They do not affect whether an index is used when resolving an ORDER BY or GROUP BY.

по всей видимости ты все-таки попадаешь на random reads.
iostat в руки и смотреть.

 

[Wicked]

Автор оригинала: SelenIT AFAIK, при составном индексе еще как играет.

Тогда может ты мне расскажешь, что мешало анализатору запросов/оптимизатору mysql заменить group by field2, field1 на group by field1, field2 ?

 

[Найч]

Wicked
а не измениться ли смысл запроса?

 

[Wicked]

Найч: с чего вдруг?

 

[Найч]

Например, поэтому

If you use GROUP BY, output rows are sorted according to the GROUP BY columns as if you had an ORDER BY for the same columns