Menu

The query that took too long
📰

The query that took too long

DEV Community: database·Israel Negrete Lepe·9 days ago
#UoqQLmZL
#devtodatabase#devto#dev#rows#index
Reading 0:00
15s threshold

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.…

Continue reading — create a free account

Join HashtagPLUS to read full articles, follow hashtags, vote, and join the conversation.