Оптимизация пагинации с фильтрами

[xrob]

Здравствуйте, друзья! Долгое время не обращался за помощью - справлялся сам.
Но сейчас хочу спросить совета у знающих людей.

----------------------------

Стоит задача сделать вывод записей с пагинацией.
Выборка конкретной страницы делается с помощью SELECT LIMIT OFFSET, тут все понятно.

Но как понять, сколько всего будет страниц?
Смотрел примеры пагинаторов в интернете.
Да, дополнительный запрос COUNT - это, конечно, замечательно.

Но давайте представим, что нам нужны записи, подходящие по определенным параметрам:
show.php? param1=111 & param2=222 & param3=333 и т.д.

Получается, что базе придется дважды проверять записи по нашим условиям - сначала в SELECT, потом в COUNT,
и если условие достаточно сложное, то это уже становится проблемой.

Можно ли это как-то оптимизировать?
Я придумал такой вариант:

1. делаем выборку всех подходящих записей: SELECT id FROM items WHERE (условия).
теперь мы знаем, сколько всего будет записей/страниц.
2. получаем записи для конкретной страницы по их id: SELECT * FROM items WHERE (id=5) OR (id=48) OR ... и т.д.

Таким образом мы избавимся от повторной проверки условий.

И тут возникает следующий момент: нашему ненасытному пользователю мало первой страницы и он запрашивает вторую, третью...
Нам снова приходится проверять все записи в базе по условиям - то, что уже делалось ранее.

И вот я думаю, имеет ли смысл сохранить результат первого шага - пачку id-шников подходящих нам записей?
Тогда при запросе последующих страниц нам не придется делать выборку по условиям, а можно будет сразу получать конкретные записи по известным id.

Понятно, что при изменении таблицы items, выборку по условию все-таки нужно будет переделывать ибо сохраненная выборка может потерять свою актуальность.
(я полагаю, что меняться данные будут значительно реже, чем просматриваться)

-------------------------------

Кто что думает по предложенным идеям?
Мне они кажутся хорошими, но, возможно, я что-то упускаю или все уже придумано до меня?
Как сделать правильно, чтобы потом у виска не крутили?

[ADSoft]

в каком месте оптимизация то?
1) выборка ВСЕХ данных - напрочь убивает сам смысл пагинации
2) да еще вы потом добавляете кучу OR .... (хотя было бы логичнее использовать IN())
все это будет работать ГОРАЗДО дольше, чем стандартный механизм COUNT и потом LIMIT/OFFSET

Есть еще SQL_CALC_FOUND_ROWS почитайте https://habr.com/ru/post/64655/

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

А еще - не надо оптимизировать преждевременно, возможно ваш проект никогда до таких нагрузок и не доживает )))) ну ради интереса посмотрите EXPLAIN COUNT() как считает )))

[xrob]

ADSoft, благодарю за ответ, вы мне помогли!
SQL_CALC_FOUND_ROWS - как раз то, что нужно.

Оптимизацию я тут вижу в том месте, что мы ушли от двойной проверки условий (LIMIT + COUNT).

1) выборка ВСЕХ данных - напрочь убивает сам смысл пагинации

Я беру не все данные, а только одно поле (id).
А основной смысл пагинации вижу в том, чтобы клиенту выдавать информацию порционно и с этой точки зрения предложенный мной способ не отличается от классического - ведь клиенту то отдается одна страница, а не вся выборка.

2) да еще вы потом добавляете кучу OR...

Каюсь, грешен. За IN() спасибо! Совсем про нее забыл, действительно лучше чем штабеля OR, хотя бы синтаксически.
Но при этом имею склонность не согласиться с тем, что будет сильно дольше, чем COUNT + LIMIT/OFFSET, пусть даже и с OR.

Ведь я проверяю условия только в первом запросе, а вторым получаю небольшое кол-во записей (сколько нужно на странице),
а при стандартном подходе условия проверяются и в COUNT-запросе и, частично, в SELECT.

Ну и я не столько оптимизирую под большие нагрузки, сколько стараюсь избежать дилетантского подхода, коим мне казался оказавшийся приемлемым LIMIT + COUNT.
Это как если бы в 3д-приложении текстуры грузились бы с диска на каждую отрисовку кадра - т.е. проделывается явно лишняя работа.

[ADSoft]

любые выводы надо делать исходя из практически доказанных результатов
... сделайте свой вариант - посмотрите explain, засеките время
- классический вариант - то же самое
ну и сравните

[ADSoft]

Сообщение от xrob

Цитата:
1) выборка ВСЕХ данных - напрочь убивает сам смысл пагинации
Я беру не все данные, а только одно поле (id).

не суть важно .... будет БД с миллионом записей - вы будете миллион значений забирать, а COUNT() только одно!