Как сделать быструю пагинацию с сортировкой по дате?

Question

[Wade2k]

Приветствую.
Нужно сделать быструю пагинацию по таблице комментариев на сайте по дате
В таблице всего 450тыс комментариев

Таблица вида
id - int|comment - varchar|dt - date

Выбор страницы

SELECT * FROM comment order by dt desc LIMIT 50 OFFSET {page*50}

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

Индекс на dt есть, но в explain он не попадает

Как сделать такую паганицию быстрой? С учетом того, что комментарии бывают удаляются и просто сделать SELECT * FROM comment WHERE id > {page*50} LIMIT 50 не получится?

Остается держать страницы пагинации и idшники для каждой странице в каком нибудь кеше?

Или я что то упустил?

Answer 1

[nickerlan]

Навскидку

SELECT * FROM comment WHERE id > {LAST_COMMENT_INDEX} LIMIT 50

Где LAST_COMMENT_INDEX это индекс последнего коммента с предыдущей страницы

Comments

[Wade2k]

А где его взять? Хранить в кеше page_id-last_comment_id?

[Иван Шумов]

Wade2k, не слушайте его auto increment primary key int не гарантирует порядок возврата их. Особенно если вы удаляете данные из таблицы

[nickerlan]

Wade2k я бы с фронтенда присылал айдишник последнего коммента в обратку при нажатии кнопки "загрузить еще", чтобы оставаться stateless на стороне сервера.
Если же зачем-то (не пойму зачем) нужны эти архаичные переходы с первой страницы сразу на четвертую - вычислял бы айдишники с точностью до погрешности на их удаление относительно текущей страницы

[Wade2k]

nickerlan, спасибо. Думаю об этом варианте.

[Иван Шумов]

nickerlan, auto increment primary key int может записываться в базу в таком порядке: 1000, 1001, 1002, 1003, 5, 1004. Что вы с этим сделаете в вашем подходе?)

[nickerlan]

Иван Шумов, для начала глянул бы на масштаб бедствия в текущей базе - прогнал бы тест. Есть же реальные данные.
Если там это совсем уж отдельные случаи - просто забил бы.
Если чуть чаще - может костыль бы какой-то прикрутил. Например, искал бы недостающие элементы последовательности отдельным запросом, если таковые встречаются и выкидывал бы лишние. Конечно же, думая, ну нафига удаление делается удалением а не флагом deleted?

И только потом бы стал ковыряться с индексами. Но Ваш подход - правильнее, даже не спорю

Answer 2

[Иван Шумов]

mysql на больших объемах может игнорировать строковые индексы (да-да индекс по datetime строковый в целом). Есть 2 варианта:
- попробовать сделать индекс по отдельному полю с unix_timestamp (с int все должно прокатить по идее)
- запихать в индекс все поля, которые вы получаете в запросе (тогда вы не оставите ему выбора, но индекс будет значительно более тяжел)

Comments

[Wade2k]

По второму варианту не понял. Что именно запихать?

[Иван Шумов]

Wade2k, про составные индексы знаете? берете и делаете индекс, включающий в себя все поля, которые вы возвращаете из таблицы в запросе (в вашем случае все). Первым полем в индексе ставите `dt`

[Wade2k]

Иван Шумов, конечно знаю. По идее чтоб использовался индекс он должен быть в where или order by. Но все равно попробую так

[Иван Шумов]

Wade2k, составной индекс он на то и составной что может использоваться как любой набор в порядке указания в индексе. Например, у вас есть поля:
id, name, created_at
и индекс: created_at, name, id

так вот он попадет в запросы где используется в любой фильтрации или сортировке где есть варианты:
- created_at, name, id
- created_at, name
- created_at

[Wade2k]

Так они в where должны быть. А у меня и where нет, только order by по одному полю dt. Поэтому и не понял зачем составной индекс

[Иван Шумов]

Wade2k, не обязательно. к сортировке тоже применимо

Answer 3

[ThunderCat]

хак