Оптимизация ORDER BY — о чем многие забывают

На тему оптимизации MySQL запросов написано очень много, все знают как оптимизировать SELECT, INSERT, что нужно джоинить по ключу и т.д. и т.п.

Но есть один момент, тоже неоднократно описанный во всех мануалах, но почему-то про него все забывают.

Оптимизация ORDER BY в запросах с джоинами.

Оправдание: поиском воспользовался, не нашел !

Большинство считают, что если ORDER BY происходит по индексу, то и проблем никаких нет, однако это не всегда так. Недавно я разбирался с одним запросом который дико тормозил базу хотя вроде все индексы на нужных местах. ORDER BY оказался последним местом, куда я ткнулся, и проблема оказалась именно там.

Маленькая выдержка из мануалов по оптимизации:

===
Как MySQL оптимизирует ORDER BY
Ниже приведены некоторые случаи, когда MySQLне может использовать индексы, чтобы выполнить ORDER BY
…
Связываются несколько таблиц, и столбцы, по которым делается
сортировка ORDER BY, относятся не только к первой неконстантной
(const) таблице, используемой для выборки строк(это первая таблица
в выводе EXPLAIN, в которой не используется константный, const, метод выборки строк).
…
===

Для ORDER BY важно, чтобы таблица, по которой будет производиться сортировка была на первом месте. Однако по умолчанаю, в каком бы порядке вы не джойнили таблицы, встроенный в mysql оптимизатор переставит их в том порядке, как он сам посчитает нужным. То есть если вы поставили нужную таблицу первой в запросе, то это вовсе не означает, что она будет на самом деле первой.

К счастью, оптимизатору mysql можно сказать, чтобы он джоинил таблицы в том порядке, какой мы ему указали, для этого нужно в SELECT добавить команду STRAIGHT_JOIN:

SELECT STRAIGHT_JOIN… FROM table JOIN…… ORDER BY table.row

Проверка на mysql базе форума PHPBB3 содержащей около 300 000 постов:

SELECT t.*, p.*, u.username 
FROM phpbb3_topics as t, phpbb3_posts as p,  phpbb3_users as u 
WHERE t.topic_replies>0 AND p.poster_id=u.user_id AND topic_first_post_id<>p.post_id AND topic_approved=1 AND p.topic_id=t.topic_id AND t.forum_id='16' AND p.post_id<'244103' 
ORDER by post_id desc LIMIT 40

Query took 12.2571 sec

в explain видим ужасное: Using where; Using temporary; Using filesort

Меняем порядок таблиц (кеш мускуля сбросил перезагрузкой):

SELECT STRAIGHT_JOIN t.*, p.*, u.username 
FROM phpbb3_posts as p, phpbb3_topics as t, phpbb3_users as u 
WHERE t.topic_replies>0 AND p.poster_id=u.user_id AND topic_first_post_id<>p.post_id AND topic_approved=1 AND p.topic_id=t.topic_id AND t.forum_id='13' AND p.post_id<'234103' 
ORDER by post_id desc LIMIT 40

Query took 0.0447 sec

в explain: Using where;

Вот такой принудительной перестановкой таблиц мы ускорили выполнение запроса в 300 раз!

Это совсем не значит, что нужно всегда использовать STRAIGHT_JOIN и самому следить за порядком таблиц. Но в некоторых случах это необходимо.

P.S. Этот запрос используется Яндексом для индексации форумов phpbb. До оптимизации, яндекс-бот клал сервер php.ru каждую ночь на несколько часов (сервер не очень мощный). В блоге Яндекса была дисскуссия на эту тему, но она закрыта пару лет назад и решение там не озвучено.