filesender: Graph display is very slow and uses a lot of memory

When there are many transfer histories, the graph display on the upload screen is very slow and uses a lot of memory on the MySQL database. In our site’s case, it is normally over 20 seconds.

Unfortunately, our site went down because it ran out of memory when multiple users displayed the upload screen simultaneously. So, we don’t show the graph now.

• Number of rows in Transfers table; about 40,000

• Number of rows in AuditLogs table; about 800,000

• Number of rows in StatLogs table; 13,843,255

• SQL

SELECT days.date, speed.speed, speed.enspeed FROM (SELECT (SELECT Date(NOW() - INTERVAL '30' DAY)) + INTERVAL a+b DAY date
FROM (SELECT 0 a UNION SELECT 1 a UNION SELECT 2 UNION SELECT 3 UNION SELECT 4
UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9 ) d,
(SELECT 0 b UNION SELECT 10 UNION SELECT 20 UNION SELECT 30 UNION SELECT 40) m
WHERE (SELECT Date(NOW() - INTERVAL '30' DAY)) + INTERVAL a+b DAY <= (select date(now()))
ORDER BY a + b) as days LEFT  JOIN (SELECT DATE(created) as date,
AVG(case WHEN time_taken > 0 AND additional_attributes LIKE '%\"encryption\":false%' THEN size/time_taken ELSE null END) as speed,
AVG(case WHEN time_taken > 0 AND additional_attributes LIKE '%\"encryption\":true%' THEN size/time_taken ELSE null END) as enspeed,
AVG(case WHEN additional_attributes LIKE '%\"encryption\":false%' THEN id ELSE null END) as count,
AVG(case WHEN additional_attributes LIKE '%\"encryption\":true%' THEN id ELSE null END) as encount
from StatLogs WHERE event='file_uploaded' AND created>NOW() - INTERVAL '31' DAY AND size > 1073741824
GROUP BY Date) as speed on days.date=speed.date  ORDER BY days.date;