Везде, где используется LIMIT offset для больших таблиц, рано или поздно начинаются тормоза. Запросы вида
SELECT * FROM test_table ORDER BY id LIMIT 100000, 30
могут выполнятся очень долго. Например, в моем случае, на одном из сайтов кол-во комментариев перевалило за 200к и постраничная навигация по комментариям начала ощутимо тормозить, а в mysql-slow.log все чаще стали попадать запросы с временем выполнения 3-5сек.
Проблема заключается в том, что используя LIMIT 100000, 30 — mysql вначале пройдется по первым 100000 записям и только потом выберет нужные 30. Избежать этого достаточно просто, достаточно использовать подзапрос вида, который в общем случае выглядит так:
SELECT * FROM test_table JOIN (SELECT id FROM test_table ORDER BY id LIMIT 100000, 30) as b ON b.id = test_table.id
Давайте рассмотрим конкретный пример. В моем случае используется движок DLE и в нем запрос выглядит следующим образом:
SELECT dle_comments.*... FROM dle_comments LEFT JOIN dle_post ON dle_comments.post_id=dle_post.id LEFT JOIN dle_users ON dle_comments.user_id=dle_users.user_id ORDER BY id desc LIMIT 101000,30
Исправленный запрос выглядит так:
SELECT dle_comments.*... FROM dle_comments LEFT JOIN dle_post ON dle_comments.post_id=dle_post.id LEFT JOIN dle_users ON dle_comments.user_id=dle_users.user_id JOIN (select id FROM dle_comments ORDER BY id desc LIMIT 101000,30 ) as t ON t.id = dle_comments.id
На графике можно увидеть результат такой замены:
Как видно, с использованием JOIN производительность сохраняется на нужном уровне не зависимо от того как далеко забрался пользователь в дебри сайта используя постраничную навигацию.
PS. Фикс для DLE для комментариев (аналогичным образом можно сделать для всех навигаций). В файле comments.class.php
найти
$sql_result = $this->db->query( $this->query . " LIMIT " . $this->cstart . "," . $this->comments_per_pages );
заменить эту строчку на:
$sql_result = $this->db->query( str_replace("ORDER BY id desc", "JOIN (select id FROM " . PREFIX . "_comments ORDER BY id desc" . " LIMIT " . $this->cstart . "," . $this->comments_per_pages .") as t ON t.id = " . PREFIX . "_comments.id",$this->query) );
Автор: Shannon
