Оптимизируем LIMIT offset

Везде, где используется 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 );

заменить эту строчку на:

if( $_GET['do'] == "lastcomments" )
    $sql_result = $this->db->query( str_replace("ORDER BY id desc", "JOIN (select id FROM " . PREFIX . "_comments ORDER BY id desc" . " LIMIT " . $this-else
    $sql_result = $this->db->query(  $this->query . " LIMIT " . $this->cstart . "," . $this->comments_per_pages );
>cstart . "," . $this->comments_per_pages .") as t ON t.id = " . PREFIX . "_comments.id",$this->query) );