Как сделать выборку с LIMIT и подсчет записей одним запросом?

Question

[Сергей]

Задача типичная, пагинация записей из базы.
Сделать выборку записей с лимитом и подсчитать сколько записей всего попадает под это условие.
Стандартное решение:

SELECT * FROM users WHERE status > 0 limit 10;
SELECT COUNT(*) FROM users WHERE status > 0;

Но 2 запроса к базе не есть хорошо, а если основной запрос тяжелый с Join'ами и вложенными запросами, то вдвойне не хорошо. Вроде есть решение с SQL_CALC_FOUND_ROWS, но почему-то на mariaDB 10.1.44 не работает, а в MySQL 8.0 вообще судя по официальному сайту объявлено устаревшим.

Кто как решает данную проблему? На данный момент я использую классический метод с 2-мя запросами и кэширую результаты подсчета средствами PHP + memcached для не часто обновляемых данных. Т.е второй запрос на подсчет количества записей делаю не всегда.

Answer 1

[Andrey Tsvetkov]

SELECT count(*) over() as total, status FROM users WHERE status > 0 limit 10;

Comments

[Сергей]

MariaDB нецензурно выругалась на этот запрос:

У вас ошибка в запросе. Изучите документацию по используемой версии MariaDB на предмет корректного синтаксиса около '() as total

[Andrey Tsvetkov]

Не знаю, что там с вашей MariaDB, но у меня в докере поднята mariadb:10.3 и в ней всё работает, MySQL 8 так же работает

[galaxy]

И задачу не решает - 10 максимум будет в count

[Andrey Tsvetkov]

galaxy, серьёзно? вы вообще в курсе как работают оконные функции?

[galaxy]

Andrey Tsvetkov, серьезно затупил, с кем не бывает :(

Answer 2

[Дмитрий Энтелис]

1. SQL_CALC_FOUND_ROWS использовать нельзя вообще. В отличии от count это безумно медленно.
https://bugs.mysql.com/bug.php?id=19553

2. Два запроса это по дефолту правильное решение.
- количество запросов стоит оптимизировать когда их получаются сотни. Заменять 2 запроса на 1 - такая себе оптимизация.
- запрос count зачастую можно оптимизировать относительно запроса с данными убрав лишние таблицы.

3. Любые кеши стоит вводить в проект только когда у вас база реально перестает справляеться.

4. В реальной задаче с пагинацией основная боль это большие offset :)

Comments

[Сергей]

В моей ситуации со всеми Join'ами и вложенными запросами основной запрос выполняется примерно 3 секунды. Соответственно второй запрос COUNT почти удваивает это время.

[galaxy]

А вы делайте limit в приложении: доставайте только первые 10 записей :)

[Сергей]

galaxy, плодить тысячи объектов моделей?

[galaxy]

Сергей, Нет. До создания моделей лимит проверяйте. А общее количество через mysql_num_rows()

[Пашенька]

galaxy, вы рекомендуете старьё, выпилинное в похапе 7+.

[galaxy]

Пашенька, Придирка ок
Посыпаю голову пеплом, исправляюсь: mysqli_num_rows()

[Дмитрий Энтелис]

Сергей, если основной запрос выполняется 3 секунды - значит стоит начать с оптимизации этого запроса.
Разумеется если это не какая-нибудь лютая статистика с агрегацией 10+ млн записей - тогда надо её просто закешировать в отдельную структуру.