How to optimize pagination (node, mysql)?

Question

MySQL

There is some original query Q, now I do
1) select count(*) as total from (select * from (Q) t)
2) Q limit ? offset?
Because of this, the execution time increases at least 2 times.
If you do pagination programmatically - something like _.drop(data, offset).slice(0, pageSize) - then it comes out faster, but if there are a couple of million lines, then this is probably a bad decision.
What are some good practices for pagination? Ideally, I would like total and everything else to be executed in 1 request and that mysql somehow caches the results for requests where only offset changes ...
Or is it still correct to do pagination on the side of the node?

Answer 1

the fact is that in order to calculate the count, the muscle has to fetch all records on request, in addition, the muscle cannot cache subqueries, so it can cache the result of the request

select count(*) as total from (select * from (Q) t)

, but when this cache becomes obsolete, it will again begin to evaluate the subquery completely and fetch all entries to calculate count.
further the muscle is not able to optimize limit, offset. to return the result with offset=1000000, it will again fetch all previous records. fetch is essentially a record in a temporary table, the maximum size of this table in memory is determined by the configuration, and when it is exceeded, the muscle will start writing to disk, and this is the brakes.
the correct solution is denormalization, in which the result will be put in a separate table, to which queries for count (*) and limit, offset will already be executed with a selection of ONLY PRIMARY KEYS and a second query for records by the values ​​of these keys. then everything will work quickly and without subd and application level caches. select * from (Q) t

Answer 2

Believe it or not, mysql can and does cache queries. But in your case, either the problem is in the table (indexes), or in the data (if there is text, then goodbye indexing). In any case, read explain and if there are no problems or nothing bothers you, cache the results in memcached, for example, on your own