MySQL query caching

Question

I am creating a fairly large statistics system that should allow users to request statistics for a given set of filters (for example, a date range).

eg. This is a simple query that returns 10 results, including player_id and the number of kills made by each player:

SELECT player_id, SUM(kills) as kills
FROM `player_cache`
GROUP BY player_id
ORDER BY kills DESC
LIMIT 10
OFFSET 30

The above query will compensate for the results by 30 (i.e. on the third page) results. When the user then selects the Next page, he will use OFFSET 40 instead of 30.

My problem is that nothing is cached, even if the LIMIT / OFFSET pair is used in the same dataset, it does SUM () again, just to compensate for the results by another 10.

The above example is a simplified version of a much larger query that simply returns more fields and takes a very long time (20 + seconds, and will only increase as the system grows).

Therefore, I am looking for a solution to speed up page loading by caching the state before applying LIMIT / OFFSET.

Answer 1

Of course, you can use caching, but I would recommend caching the result, not the query in mysql.

But first, first make sure that a) you have the correct indexing of your data, b) that it uses.

, , , , // .

/ .., . PHP , .

Answer 2

? , JOIN GROUPING. , .

, LIMIT/OFFSET, .

Answer 3

, , . :

• ( ) .
• .
• (, )
• .

, , :

• ,
• /, , .