The query endpoints of a transactional platform were returning HTTP 504: timeouts, with pagination and without it. The table was large—more than eight million rows in 2026 alone—and the first temptation was to think that explained everything: more rows, more time, more server.
It did not. The problem was not reading a large table, but how MySQL joined, filtered and sorted the data to answer that query. That is what this note is about.
The figures in the examples are illustrative: the plans in the plan reader are reconstructed, and the growth model is a model, not a measurement. From the case come the 8,040,017 records; the 1,792 and 54,898 rows, EXPLAIN estimates (the latter from the variant with hints explained below); the 17.05 s it took to create the index; and the 4.5 ms that EXPLAIN ANALYZE measured in a lab test, not in production.
A 504 does not say where the problem is
A 504 comes from the load balancer or proxy in front of the application when it gets tired of waiting.…