How to make a selection with LIMIT and counting records in one query?

Question

MySQL

The task is typical, pagination of records from the database.
Make a selection of records with a limit and count how many records in total fall under this condition.
Standard Solution:

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

But 2 queries to the database are not good, and if the main query is heavy with Joins and nested queries, then it is doubly not good. It seems that there is a solution with SQL_CALC_FOUND_ROWS, but for some reason it does not work on mariaDB 10.1.44, and in MySQL 8.0, judging by the official site , it is generally declared obsolete.

Who solves this problem? At the moment I'm using the classic 2-query method and caching the count results using PHP + memcached for infrequently updated data. Ie I do not always do the second request for counting the number of records.

Answer 1

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