Форум программистов
 
Регистрация на форуме тут, о проблемах пишите сюда - alarforum@yandex.ru, проверяйте папку спам! Обязательно пройдите активизацию e-mail, а тут можно восстановить пароль.

Как купить рекламу на форуме


Вернуться   Форум программистов > Web программирование > SQL, базы данных
Регистрация

Восстановить пароль
Повторная активизация e-mail

Купить рекламу на форуме 20000 рублей в месяц

Ответ
 
Опции темы Поиск в этой теме
Старый 23.03.2021, 10:51   #1
xrob
Форумчанин
 
Регистрация: 18.10.2010
Сообщений: 419
По умолчанию Оптимизация пагинации с фильтрами

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

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

Стоит задача сделать вывод записей с пагинацией.
Выборка конкретной страницы делается с помощью 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, выборку по условию все-таки нужно будет переделывать ибо сохраненная выборка может потерять свою актуальность.
(я полагаю, что меняться данные будут значительно реже, чем просматриваться)

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

Кто что думает по предложенным идеям?
Мне они кажутся хорошими, но, возможно, я что-то упускаю или все уже придумано до меня?
Как сделать правильно, чтобы потом у виска не крутили?
xrob вне форума Ответить с цитированием
Старый 23.03.2021, 11:40   #2
ADSoft
Старожил
 
Регистрация: 25.02.2007
Сообщений: 3,830
По умолчанию

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

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

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

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

Последний раз редактировалось ADSoft; 23.03.2021 в 12:01.
ADSoft вне форума Ответить с цитированием
Старый 23.03.2021, 19:06   #3
xrob
Форумчанин
 
Регистрация: 18.10.2010
Сообщений: 419
По умолчанию

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

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


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

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

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

Ну и я не столько оптимизирую под большие нагрузки, сколько стараюсь избежать дилетантского подхода, коим мне казался оказавшийся приемлемым LIMIT + COUNT.
Это как если бы в 3д-приложении текстуры грузились бы с диска на каждую отрисовку кадра - т.е. проделывается явно лишняя работа.
xrob вне форума Ответить с цитированием
Старый 24.03.2021, 08:29   #4
ADSoft
Старожил
 
Регистрация: 25.02.2007
Сообщений: 3,830
По умолчанию

любые выводы надо делать исходя из практически доказанных результатов
... сделайте свой вариант - посмотрите explain, засеките время
- классический вариант - то же самое
ну и сравните
ADSoft вне форума Ответить с цитированием
Старый 24.03.2021, 08:31   #5
ADSoft
Старожил
 
Регистрация: 25.02.2007
Сообщений: 3,830
По умолчанию

Цитата:
Сообщение от xrob Посмотреть сообщение

Цитата:
1) выборка ВСЕХ данных - напрочь убивает сам смысл пагинации
Я беру не все данные, а только одно поле (id).
не суть важно .... будет БД с миллионом записей - вы будете миллион значений забирать, а COUNT() только одно!
ADSoft вне форума Ответить с цитированием
Ответ
Опции темы Поиск в этой теме
Поиск в этой теме:

Расширенный поиск


Похожие темы
Тема Автор Раздел Ответов Последнее сообщение
Аккордеон и модуль пагинации в нем: как оставить раскрытую панель открытой Blondy PHP 6 16.05.2014 21:39
работа с фильтрами Lider183A Microsoft Office Excel 1 01.09.2009 12:21
Ячейки с фильтрами eda Microsoft Office Excel 5 29.07.2009 13:28
проблема с фильтрами и суммой: dimmor Microsoft Office Excel 5 25.06.2009 14:10


Проекты отопления, пеллетные котлы, бойлеры, радиаторы
интернет магазин respective.ru
Пеллетный котёл Emtas
котлы EMTAS