[Performance] 504 Gateway Timeout on Server Statistics API due to missing covering index

[Catchabox]

Description
I encountered a persistent 504 Gateway Timeout error on the admin dashboard (/v1/admin/console/server) when the traffic_log table grows large.

In my environment, the traffic_log accumulated about 5-10 million rows in just 7 days. The aggregation query used to calculate server traffic stats takes over 70 seconds to execute, causing the Nginx/Gateway to timeout.

Root Cause Analysis
I checked the MySQL slow query log and found the bottleneck:

SELECT server_id, SUM(download + upload) AS total, SUM(download) AS download, SUM(upload) AS upload 
FROM `traffic_log` 
WHERE timestamp BETWEEN '...' AND '...' 
GROUP BY `server_id` ...

Even though there might be an index on timestamp, the query performs a SUM on download and upload columns. This forces MySQL to perform a table lookup (回表) for millions of rows to retrieve the values, which causes massive I/O overhead.

Solution / Fix
I manually added a Covering Index that includes the sum fields. This allows MySQL to calculate the result directly from the index tree without reading the table data.

The query time dropped from ~70s to <0.1s immediately.

Here is the SQL command I used:

ALTER TABLE traffic_log ADD INDEX idx_traffic_stat_cover (timestamp, server_id, download, upload);