При прокрутке данных, поступающих из БД, вам нужно знать, сколько страниц будет показано для элементов управления переходом страницы.
В настоящее время я делаю это, выполняя запрос дважды, после того, как он был завершен в count() чтобы определить общие результаты, и второй раз с ограничением, применяемым для получения только тех результатов, которые мне нужны для текущей страницы.
Это кажется неэффективным. Есть ли лучший способ определить, сколько результатов было бы возвращено до применения LIMIT ?
Я использую PHP и Postgres.
С 2008 года ситуация изменилась. Вы можете использовать функцию окна, чтобы получить полный счетчик и ограниченный результат в одном запросе. (Представлено с PostgreSQL 8.4 в 2009 году ).
SELECT foo ,count(*) OVER() AS full_count FROM bar WHERE <some condition> ORDER BY <some col> LIMIT <pagesize> OFFSET <offset>
Обратите внимание, что это может быть значительно дороже, чем без общего счета. Все строки должны быть подсчитаны, а ярлык, содержащий только верхние строки из соответствующего индекса, невозможен.
Не имеет большого значения с маленькими столами, дела с большими столами.
Рассмотрим последовательность событий :
WHERE (и условия JOIN , но не здесь) фильтруют соответствующие строки из базовой таблицы (таблиц).
( GROUP BY и совокупные функции будут здесь).
Функции окна применяются с учетом всех квалификационных строк (в зависимости от предложения OVER и спецификации фрейма функции). Простой count(*) OVER() основан на всех строках.
ORDER BY
( DISTINCT или DISTINCT ON будет здесь).
LIMIT / OFFSET применяются на основе установленного порядка для выбора строк для возврата.
Обратите внимание, что LIMIT / OFFSET становится все более неэффективным с ростом числа строк в таблице. Рассмотрите альтернативные подходы, если вам нужна более высокая производительность:
Есть и совершенно разные подходы. Postgres имеет внутреннюю учетную запись, сколько строк, на которые повлияла последняя команда SQL. Некоторые клиенты могут получить доступ к этой информации или сами подсчитать строки (например, psql).
Например, вы можете получить число затронутых строк в plpgsql сразу после выполнения команды SQL с помощью:
GET DIAGNOSTICS integer_var = ROW_COUNT;
Подробности в руководстве.
Или вы можете использовать pg_num_rows в PHP
Примеры кода:
Как я описываю в своем блоге , MySQL имеет функцию SQL_CALC_FOUND_ROWS . Это устраняет необходимость выполнения запроса дважды, но он все равно должен выполнять запрос во всей полноте, даже если предложение limit позволило бы ему остановить раньше.
Насколько я знаю, подобной функции для PostgreSQL нет. Одна вещь, которую следует учитывать при выполнении разбивки на страницы (наиболее распространенная вещь, для которой используется LIMIT IMHO): выполнение «OFFSET 1000 LIMIT 10» означает, что БД должно извлекать не менее 1010 строк, даже если это дает вам только 10. Более эффективный способ – запомнить значение строки, которую вы заказываете для предыдущей строки (в данном случае 1000), и переписать запрос следующим образом: «… WHERE order_row> value_of_1000_th LIMIT 10». Преимущество состоит в том, что «order_row», скорее всего, индексируется (если нет, вы столкнулись с проблемой). Недостаток заключается в том, что если новые элементы добавляются между просмотрами страниц, это может немного немного синхронизироваться (но опять же, это может быть не наблюдаемым посетителями и может быть большим приростом производительности).
Увидев, что вам нужно знать для целей пейджинга, я предлагаю выполнить полный запрос один раз, записывая данные на диск в виде кеша на стороне сервера, а затем загружая его через ваш поисковый механизм.
Если вы выполняете запрос COUNT с целью решить, предоставлять ли данные пользователю или нет (т.е. если есть записи X, верните ошибку), вам необходимо придерживаться подхода COUNT.
Вы можете уменьшить штраф за производительность, не выполняя каждый раз запрос COUNT (). Загрузите количество страниц, скажем, за 5 минут до повторного запуска запроса. Если вы не видите огромное количество INSERT, это должно работать нормально.
Поскольку Postgres уже выполняет определенное количество кеширования, этот тип метода не так неэффективен, как кажется. Это определенно не удваивает время исполнения. У нас есть таймеры, встроенные в наш уровень БД, поэтому я видел доказательства.