На тему оптимизации MySQL запросов написано очень много, все знают как оптимизировать SELECT, INSERT, джоинить по ключу и т.д. и т.п.
Но есть один момент, тоже неоднократно описанный во всех мануалах, но почему-то про него все забывают.
Оптимизация 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_idp.post_id AND topic_approved=1 AND p.topic_id=t.topic_id AND t.forum_id='16' AND p.post_id0 AND p.poster_id=u.user_id AND topic_first_post_idp.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 40Query took 0.0447 sec
в explain: Using where;
Вот такой принудительной перестановкой таблиц в запросе мы ускорили выполнение в 300 раз !
Вроде очевидная вещь, но за свой многолетний опыт ковыряния в php-проектах ни разу не встречал чтобы им пользовались…
P.S. На php.ru этот запрос используется Яндексом для индексации форума. До оптимизации, на не очень мощном сервере, яндекс-бот клал сервер каждую ночь на несколько часов. В блоге Яндекса была дисскуссия на эту тему (индексация форумов phpbb) но она закрыта пару лет назад и решение там не было озвучено.
