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

[SkazochNik]

Как оказалось боян.

[barker]

Ну, тут не столько боян, просто это весьма известная и насущная проблема, и, соответственно, решений в инете кучу можно найти.
Вот ещё, например: habrahabr.ru/post/44608/

[tagir_valeev]

В боянах тоже польза есть. Я вот не знал, и мне может пригодиться. Спасибо.

[Vertexodessa]

Я слышал, что Oracle && MSSQL сами такие запросы оптимизируют, и проблема есть только в MySQL. Насколько это правда?
P.S. ну и, хотелось бы отметить, что такие запросы обычно — результат неверной разработки пользовательской модели… Ведь что такое LIMIT 100000, 10? это 10000-ная страница выборки, представьте как надо пользователя замучить, чтобы он что-то искал на 10000-ной странице

[ingrysty]

Сокровища пиратов на 10001 странице, но для этого нужно пролистать все с самого начала! ;)

[lenar]

Например, самые новые сообщения в определенной теме форума — на последней странице. А их может быть и 100, и 1000, и, кто знает, может и 10000.

[Vertexodessa]

возможно, есть смысл менять сортировку(при этом, конечно, необходимы некоторые манипуляции с offset)… Или привязаться к другим полям, предварительно их проиндексировав, если есть такая возможность. (SELECT id FROM table WHERE date < '01-01-2014' AND date > '01-03-2014')

(если кто-то подумал что автор предложил 100% решение проблемы — это не совсем так, попробуйте запустить SELECT id FROM table LIMIT 10000000,40 на базе с 20 000 000 записей)

[macik_spb]

То рискует не найти нужной ему информации, т.к. в темах с таким количеством комментариев они быстро обновляются и «уходят» вниз.

[pihel]

Oracle rownum считает до сортировки, т.е. там нужно писать так:

select * from (select c.* from comments c ORDER BY id) v WHERE rownum BETWEEN 10000 and 10010

Mysql в этом плане гораздо удобней.
Насчет оптимизации не знаю, нет возможности сейчас проверить план.

[akzhan]

Во многих случаях MySQL способен использовать индексы для сортировок и для LIMIT.

[Vertexodessa]

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

[vsespb]

Результат же будет отличаться если в запросе есть условие (и не только).
Например, если в оригинальном запросе первая запись результата найдётся на миллионной записи БД.
То во втором запросе она вообще не найдётся, т.к. её нет среди первых 100 000.

[Vertexodessa]

devoluk.com/mysql-limit-offset-performance.html
Не согласен с тем, что результаты запроса будут отличаться

[vsespb]

Можете объяснить в чём конкретно ошибка в моих рассуждениях? В обоих статьях нет реальных примеров с where.

[Vertexodessa]

обьясню как оно должно выглядеть при наличии WHERE
первый запрос:
SELECT id FROM table WHERE date… LIMIT…
второй запрос:
JOIN SELECT * FROM TABLE WHERE id IN…

[vsespb]

create table test_table ( id int(11), f int(11), key f(f), primary key (id));
insert into test_table set id=1, f = 1;
insert into test_table set id=2, f = 1;
insert into test_table set id=3, f = 1;
insert into test_table set id=4, f = 2;
insert into test_table set id=5, f = 2;

SELECT * FROM test_table WHERE f = 2 ORDER BY id LIMIT 1, 1;

(выдаёт id=5)

SELECT * FROM test_table JOIN (SELECT id FROM test_table ORDER BY id LIMIT 1, 1) as b ON b.id = test_table.id WHERE f = 2 ;

(WHERE только во внешнем запросе: ничего не выдаёт)

Если where будети во внутреннем запросе — бенчмарки будут другие, думаю. К тому-же вместо where может быть JOIN (не LEFT JOIN)

[Vertexodessa]

перенесите WHERE во внутренний запрос и заработает… По поводу бенчмарков — лично тестировал — ускорение при разбивке на два запроса существенное.

[vsespb]

Судя по этой статье explainextended.com/2009/10/23/mysql-order-by-limit-performance-late-row-lookups/ Вы правы, при этом ускорении происходит не из-за того что внутренний запрос простой и планировщику легче понять какой индекс использовать, а из-за того что mysql меньше обращается в саму таблицу, обходится только индексами (что не очевидно для меня из данного поста), так же ускорение будет только если условие во внутреннем запросе можно вычислить только с использованием индексов.

[vit1251]

А можно Вас попросить произвести запрос с явным указанием колонок, то есть использовать не *, а явное перечисление, так как я думаю, что вы заставляете MySQL производить сначала полное перечисление всех записей для выделение столбцов, а потом уже непосредственно выборку.

[VladimirAndreev]

мм, если комментов очень много, наверное, стоит создать таблицу вида

[pk] theme_id
[pk] page_id
[pk+uq] comment_id

по запросу в нее получать список ID всем комментов, которые нужны…
так как таки читают комменты обычно намного больше, чем удаляют. да и построение пагинатора не потребует каунтов и беспокойства самой таблицы постов

[bat]

Проблема заключается в том, что используя LIMIT 100000, 30 — mysql вначале пройдется по первым 100000 записям и только потом выберет нужные 30.

Не совсем так. В общем случае, будут выбраны все записи, попадающие под условие where (тут его нет), отсортированы, затем будут отброшены первые 100 тыс записей и выданы следующие 30. Скорее всего, в данном случае сортировки не будет, так как сортировка по индексному полю, но все равно в пустую будут прочитаны 100тыс записей и для них будут выполнены соединения, отсюда и линейный рост времени выполнения.

Избежать этого достаточно просто

Да просто, сортировка и лимит будут выполнены только за счет индекса, но это применимо только к этому запросу. Мне он кажется весьма странным — постраничная лента всех комментариев в базе (мне не понятно зачем это). Если в этот запрос добавить доп условие, например, ограничение по посту, то указанный хак перестанет быть таким эффективным, поскольку в запросе вида
select id from comments where post_id = 123 order by id limit 10000,30
индекс может быть использован либо для сортировки либо ограничения выборки по post_id.

[kashey]

Решал такую проблему для сильно более плохого случая.
Решение простое — записываем номера страниц в комменты, а потом выбираем данные по этому ключу.
По факту — просто храним некую метаинформацию о топике с номерами первых ID на странице N, или чуть чуть сложнее.
Четко, красиво, стабильно.

[Vertexodessa]

а как быть в случае удаления коммента? шерстить все последующие на предмет обновления номера страницы? Есть какое-то решение кроме запрета удаления по прошествию Х минут?

[kashey]

Усложняем задачу — у меня древообразные комментарии.
Посему добавляем команды на «сдвиг» и «массовые» апдейты бд.
Фактически это нормально не решается средствами SQL, как минимум хранимками, так как требует анализа данных и кучи запросов как на выборку, так и на апдейт.
Просто есть ряд задач, которые не надо решать «адекамическим» путем — можно и внешних мозгов на другом языке дописать.

[VladimirAndreev]

бэкенд-скриптом пересчитывать…

[Bal]

>а как быть в случае удаления коммента?

Пересчёт номеров страниц одноразовая операция и выполняется относительно быстро. Удаление — событие нечастое, можно подождать и с пересчётом.

У меня на форуме бывает под 15 тыс. ответов в теме. До 600 страниц на топик. И это при том, что я стараюсь жёстко разделять тему на новые темы в случае её роста, так бы и многие тысячи страниц были у некоторых (а ля iXBT-style, когда найти что-то потом нереально). При реальном использовании основная масса народа постоянно пасётся именно на последних страницах, т.е. львиная масса запросов именно в духе… LIMIT 10000, 25. Когда сидит хотя бы человек 200 в онлайне, нагрузка получается очень большая. Особенно, учитывая то, что сортировка не по одному ID, а по дате сообщения, приоритету сортировки, с учётом пометки «удалено» (физическое удаление в моём случае не практикуется) и ещё что-то. Индексы получаются сложные и большие. Пришлось вводит административные ограничения «200 страниц темы — создавайте новую тему» и т.п. Параметр «страницы темы» не вводил, так как тоже опасался проблемы пересчёта. Потом составил не особенно сложный запрос пересчёта номеров страниц и ввёл соответствующую сущность. Выборки стали выполняться мгновенно.

[plutov]

Я в некоторых местах перестал использовать limit-offset. Дело в том, что он не подходит для часто обновляемых данных. О своём подходе в блоге написал — plutov.by/post/cursor_pagination