Долго выполняются запросы с сортировкой (2-5 сек вместо 0.1-03)

Question

[mihalich]

Долго выполняются запросы вроде

SELECT SQL_NO_CACHE news.*, news_category.name AS category FROM news, news_category WHERE news.news_category_id = news_category.id AND news_category.id != 114 AND create_date < CURRENT_DATE AND FIND_IN_SET(50, news.region_ids) AND news.active_status = 1 AND news_category.active_status = 1 ORDER BY id DESC LIMIT 3

И даже так — добавление сортировки к любому запросу ведёт к непропорциональному увеличению времени его выполнения.

Таблицы — MyISAM

В чем может быть дело?
Прямо ступор какой-то.

Настройки тут — www.cottage.ru/mysql-params.txt (utf-8)

Сервер php-fpm+nginx

Comments

[mihalich]

без ORDER BY — всё мгновенно. Индексы нормальные и необходимы, заново даже их перевыставили.

[mihalich]

SELECT SQL_NO_CACHE news. *, news_category.name AS category
FROM news, news_category FORCE INDEX (PRIMARY)
WHERE news.news_category_id = news_category.id
AND news_category.id !=114
AND create_date < CURRENT_DATE
AND FIND_IN_SET( 50, news.region_ids )
AND news.active_status =1
AND news_category.active_status =1
ORDER BY id DESC
LIMIT 3

т.е. если жёстко указать FORCE INDEX (PRIMARY), то всё работает как надо

т.е. майскл не использует почему-то индекс при сортировке, или не тот использует

[mihalich]

Отображает строки 0 — 29 ( 13,568 всего, Запрос занял 0.0009 сек.)

Answer 1

[himik]

что говорит EXPLAIN?

Comments

[mihalich]

вот explain — d.pr/i/u6AR

[mihalich]

буферы пробовали увеличивать — не помогло, там проблема в том, что майскл почему-то просто НЕ использует индексы для сортировки.
т.е. не потому что медленно использует, а потому что вообще НЕ использует

Answer 2

[Григорий Перетяка]

Сортировка — тяжелая операция, что вас удивляет? Используйте inner join, пробуйте джойнить уже отфильтрованные данные, как-то так:

SELECT SQL_NO_CACHE news.*, news_category.name AS category FROM news
INNER JOIN news_category ON (news.news_category_id = news_category.id AND news_category.id != 114 AND news_category.active_status = 1)
WHERE AND create_date < CURRENT_DATE AND FIND_IN_SET(50, news.region_ids) AND news.active_status = 1
ORDER BY id DESC LIMIT 3

Ну и индексы не помешают.

Comments

[Григорий Перетяка]

И попробуйте так:

SELECT SQL_NO_CACHE news.* FROM news
WHERE AND create_date < CURRENT_DATE AND FIND_IN_SET(50, news.region_ids) AND news.active_status = 1
ORDER BY id DESC LIMIT 3

или так:

SELECT SQL_NO_CACHE COUNT(id) FROM news
WHERE AND create_date < CURRENT_DATE AND FIND_IN_SET(50, news.region_ids) AND news.active_status = 1

Если и тут будет тормозить, то уже только индекс поможет.
Хотя скорее всего проблема в этом:FIND_IN_SET(50, news.region_ids), очень тяжелая операция, лучше избавится от такого.

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

[Максим Барулин]

какое направление у используемых индексов? Если индекс построен как ASC то при DESC он будет все портить. Ну и от FIND_IN_SET действительно нужно уходить.

[mihalich]

дело не в индексах. на другом сервере все отрабатывает быстро. MySQL почему-то не сортирует по нужному индексу (дополнил вопрос, см. выше)

[Максим Барулин]

вы пробовали переписать запрос с использованием джоинов? Как правило они намного быстрее.

Версии mysql на обоих серверах одинаковые? Количество оперативной памяти одинаковое? Сравните explain на первом и втором сервере. Если планы одинаковые значит что-то не так с настройками mysql

Answer 3

[gaelpa]

Чтобы сортировка не тормозила запросы, нужно чтобы был доступен индекс, начинающийся с полей по которым производится сортировка и опционально продолжающийся полями фильтрации. У вас такой есть?

Comments

[mihalich]

В таблице всего 16 тыс записей, должно выполняться быстро даже без индекса

[gaelpa]

С темпом и файлсортом (в вашем эксплейне) — не факт, особенно, если буфера не хватает на то, чтобы держать это в памяти.

Answer 4

[NeX]

Попробуйте увеличить sort_buffer_size и покажите dev.mysql.com/doc/refman/5.6/en/show-profile.html для запроса

Answer 5

[mihalich]

В итоге решили проблему так — вынесли tmdir (временную директорию) в оперативку, и все наладилось.
Профилирование запроса показало, что бОльшую часть времени занимает Copying to tmp table