How to improve the performance of this MySQL 7 day rolling average query?

Question

I have the following query which calculates a 7 day rolling average, however currently it takes 4 minutes to run due to the WHERE b.serverId = a.serverId line. My question is how can I improve this query so that it takes miliseconds to seconds? The test dataset is ~250k rows.

SELECT   a.serverId,
         s.serverName,
         a.playersOnline,
         DATE(a.pingTime) AS pingDate,
         Round( ( SELECT SUM(b.playersOnline) / COUNT(b.playersOnline)
                    FROM pings AS b
                   WHERE b.serverId = a.serverId AND 
                         DATEDIFF(a.pingTime, b.pingTime) BETWEEN 0 AND 6
                ), 2 ) AS '7dayMovingAvg'
    FROM pings AS a
    JOIN `server` AS s
      ON s.serverId = a.serverId
   WHERE a.serverId = 1
GROUP BY pingDate
ORDER BY a.pingTime;

The tables:

Server table

serverId - PK, indexed

serverIp varchar

serverPort int(16)

serverName varchar

enabled tinyint(1)

Pings table

serverId FK, indexed (references Server table)

pingTime datetime, indexed

playersOnline int(5)

playersMax int(5)

Please replace textual tables description with their CREATE TABLE scripts. Add sample data in INSERT INTO scripts form (or create online fiddle), and show desired output for this data. In general - avoid correlated query. And specify precise MySQL version. — Akina, Aug 06 '20 at 05:12
See https://meta.stackoverflow.com/questions/333952/why-should-i-provide-a-minimal-reproducible-example-for-a-very-simple-sql-query — Strawberry, Aug 06 '20 at 06:48

score 0 · Answer 1 · answered Aug 06 '20 at 05:30

           WHERE b.serverId = a.serverId AND 
                 DATEDIFF(a.pingTime, b.pingTime) BETWEEN 0 AND 6

Learn about "sargeable". Then change to

           WHERE b.serverId = a.serverId AND 
                 b.pingTime BETWEEN a.pingTime
                                AND a.pingTime - INTERVAL 6 DAY

(Caution: I may have the arithmetic wrong)

Then add this 'composite' index:

INDEX(serverId, pingTime)

This does not compute:

GROUP BY pingDate
ORDER BY a.pingTime

You probably want

GROUP BY pingDate
ORDER BY pingDate

For further discussion, please provide SHOW CREATE TABLE and EXPLAIN SELECT ...

Are you recomputing all the moving averages for all days? Yesterday's won't change, will it? Store it in another table. Then compute only the current average each night.

(An 'exponential moving average' is easier and probably faster to compute. Consider switching to it.)

How to improve the performance of this MySQL 7 day rolling average query?

1 Answers1