Постраничная выборка данных — альтернативный взгляд на давно известное

[@dimzon541]

Откровенно говоря никогда не понимал желание пользователя пролистывать и глазами читать десятки/сотни страниц.
По мне правильнее ограничиться разумной по объему выборкой (ну к примеру первые 500 записей, нужно что-то конкретное — уточняйте условия фильтрации.) и сразу передать ее на клиента. А на клиенте отображать можно как угодно — можно с пагинацией, можно без. Заодно сервер приложений может быть stateless — легче масштабирование…

[@DmitryKoterov]

Может быть слишком затратно по объему передавать на клиента 500 записей. Тем более что устраивать на клиенте возможность смены сортировки среди этих 500 выбранных записей бессмысленно и чревато непониманием происходящего со стороны пользователя, если на самом деле записей должно было быть 100000.

Но в чем вы правы, так это в том, что ограничивать 500-ми (или 1000-й, как это делают гугл и яндекс) нужно. Просто надо ограничивать поиск 1001-й записью, а потом проверять: если нашлась 1001 запись, так и писать: «найдено результатов: более 1000». Это чисто интерфейсное решение, не нужно его пытаться решать на стороне базы данных.

[@dimzon541]

Ну 500 записей в json-е это обычно копейки. А сортировать на сервере естественно.

[@gleb_l]

Это чисто интерфейсное решение, не нужно его пытаться решать на стороне базы данных.

так это решение и не зависит от способа пагинации. Нужно лишь при выборке массива ключей сделать select top 1001 и посмотреть, сколько ключей приехало. Все преимущества отсутствия повторной фильтрации при навигации при этом остаются

[@DmitryKoterov]

Да, я это и имел в виду. Беспроигрышный способ. Кстати говоря, оценить приблизительноправильное число записей, которое было бы найдено, можно в ряде СУБД нативно. Например, если в постгресе сделать explain запроса, то там выдастся, сколько планировщик ожидает получить записей на выходе, и эта оценка будет (по моему опыту) весьма точной. Почему она будет точно, можно прочитать в документации, где описывается, как работает планировщик — это довольно интересное чтиво.

[@gleb_l]

Все «большие» СУБД поддерживают статистику по таблицам и индексам — это по сути, функция плотности вероятности на всем диапазоне индексируемых значений. Соответственно, если ожидается выборка по диапазону, покрытому индексом, планировщик интегрированием плотности по диапазону находит ожидаемое количество строк. Если же вы делаете неиндексируемый table scan, например по вхождению подстроки, данных об ожидаемом количестве записей планировщику принципиально взять неоткуда, и он может сильно ошибиться. Поэтому, а также из-за конечной точности ожидаемых оценок, я смотрю на эти данные, как на информацию к размышлению при анализе и оптимизации запросов к БД, но не стал бы основывать на них логику реализуемой системы.

[@bohdan-shulha]

К пункту «а», где про два запроса (COUNT и, собственно, сами записи):
Если случай не слишком сложный и ресурсы (процессорное время) как-то хочется недорого (человекочасы) сохранить, то есть замечательный (по-крайней мере, в MySQL) SQL_CALC_FOUND_ROWS, который позволяет этого избежать.
Пользовать как-то так:
> SELECT SQL_CALC_FOUND_ROWS field_1, field_2, field_n FROM table_name LIMIT @a, @b;
> SELECT FOUND_ROWS();

[@abby]

Небольшое замечание к

скажем 100 000 ключей займет на сервере приложения всего 400 кБайт

Скорее всего 800КБ, а некоторые вместо целочисленного типа в качестве ID используют UUID, что уже 1.6МБ.

[@gleb_l]

первичный ключ нужно стараться делать как можно более коротким, поэтому неоправданное использование bigint и GUID там, где достаточно инта — плохая практика. Должны быть веские аргументы их использования — например, частые массированные вставки и удаления, накручивающие identity-генератор, межбазная синхронизация, требующая GUID, использование FILESTREAM файлгруппы итд. И потом, число 100000 взято с потолка — в качестве иллюстрации того, что даже практически необрабатываемое человеком за разумное время количество записей занимает не так уж много памяти под ключи. Если на серверах приложения мало памяти — ограничьте выборку по фильтру 25000 записей — для конечного пользователя это то же самое, что 100000