NOTE · ENGLISH · OCTOBER 18, 2026

The query that took too long

Rows the first page of 50 touches in a table of 8,040,017 rows, according to a model: 8,040,217 with no useful index, 55,098 with an index on operation only and 250 with the index on operation and date. On the right, 4.5 ms measured with EXPLAIN ANALYZE inside MySQL.

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 hypothetical, 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. It does not say whether the time went into the database, the network, the application or building the response.

So the query was analyzed apart from the rest of the application, with the SQL that actually reached the database. In this case it was hand-written SQL, not generated by an ORM, but the rule is the same: to diagnose, you have to see what MySQL runs, not what you think it runs.

The table and the query

MySQL 8.0.46 on Ubuntu. Transactions live in one table per year, from 2015 to 2030; the 2026 table had 8,040,017 records when we ran the tests. Around it are lookup tables: operations, products, providers and the inbound and outbound addresses.

The query fetches one operation’s transactions for an operational report: date, time, masked phone number, identifiers, status and processing time, with the names that come from the lookup tables, ordered by date, from oldest to newest, in pages of 50. One operation had between 52,000 and 55,000 estimated rows, in a table of more than eight million.

The plan that estimated fewer rows

In the EXPLAIN of the original query, the transactions table’s row showed the IdProductos index, about 1,792 estimated rows and Using where. And the plan carried Using temporary and Using filesort.

At first sight, 1,792 looks small. But the rows column in EXPLAIN is what MySQL expects to read each time it runs that step, and the transactions table sat inside the join: that step runs once for every row the previous step hands it. Using where means it keeps only the rows that meet the condition, and a products index does not lead to one operation’s rows in date order. A small estimate at one step does not tell you how much work the whole plan is.

Then there were the two flags. In tabular EXPLAIN, when they appear together, Using temporary and Using filesort are written on the row of the first non-constant table in the plan, but they are not about that table: they say the result of the join is stored separately and sorted. They are not errors: sorting or grouping a few rows is reasonable. Here, according to the plan, they meant that to return 50 rows in date order MySQL had to gather all of the operation’s rows, store them separately and sort them before cutting: about 52,000 to 55,000 estimated rows to return 50.

The index that follows the query’s order

The query filters by operation and orders by date. An index with those two columns in that order lets MySQL find the operation’s rows already sorted, read them in order and stop as soon as the page is full, as long as the transactions table is the first in the join. It was a composite index, idx_transacciones_operacion_fecha (IdOperaciones, FechaHora).

Creating it on the 2026 table took 17.05 s in our measurement. Since the transactions are split by year, the index was also applied to the other yearly tables.

To evaluate a plan that started from the transactions, we tested a variant with FORCE INDEX (idx_transacciones_operacion_fecha) and STRAIGHT_JOIN. Both are investigation tools: FORCE INDEX makes MySQL use that index whenever it can and STRAIGHT_JOIN forces the tables to be joined in the order written. They are for comparing plans; leaving them in the code freezes a decision the optimizer can no longer correct when the data changes.

The EXPLAIN of that variant estimated about 54,898 rows up front, with no temporary table or filesort, and the lookup tables were joined with eq_ref: one exact lookup by key per row. The plan that estimated more rows did the whole job better.

Measuring: 4.5 ms, and what it leaves out

EXPLAIN is the plan before executing: what MySQL computes from its statistics. To know how long it takes you have to run it, and that is what EXPLAIN ANALYZE is for: it runs the query and reports, for each step, the time, the actual rows and the loops.

With LIMIT 50, the EXPLAIN ANALYZE of the variant with the hints recorded between 4.37 and 4.50 ms. That is the time to run the plan inside MySQL, in a lab test. It is not an end-to-end measurement of the API: it leaves out the network, the application and sending and serializing the response. Nor does it hold for the unpaginated query or for other filters, which have plans of their own.

It is not a production result either. When the tests ended, the change still had to be applied to the Node.js service and validated there. And it does not show the hints can be removed: without FORCE INDEX and STRAIGHT_JOIN, we do not know whether MySQL would pick the same plan.

What we do not know

There are questions this story does not answer, and it is worth saying so:

Production. There is no confirmation the change was deployed, nor a comparative measurement between development and production. We do know that the real volume and the distribution of records per operation mattered to the optimizer: an index that is good for one pattern may not be for another.
The plan without hints. We did not check which plan MySQL picks with the composite index and without FORCE INDEX or STRAIGHT_JOIN. Before deciding whether they stay in the code, that is what has to be measured.
Locks. There is no evidence that the query blocked writes or stalled transactions. A slow query and a lock are different problems, and they should not be confused.
What the index costs on writes. Every index is updated on every insert, every delete and whenever its columns change. We did not measure it.

When the table grows

The plan reader includes a model to see the shape of the problem. It is a row count with assumed microseconds per step, not a MySQL measurement: it is for comparing curves, not for predicting milliseconds. That is why, for the composite index, the model gives tenths of a millisecond and the case’s lab measurement gave 4.5 ms.

With a table of 8,040,017 rows, an operation of 54,898—the case’s estimate—and the transactions table first, for the first page of 50 the model gives:

How it is resolved	Rows touched
No useful index: scans everything and sorts	8,040,267
Index on operation only: finds them all and sorts	55,148
(operation, date) index: reads in order and stops	300

Each one grows with something different. The first, with the table: twice the rows, twice the work. The second, with the operation, even if the table does not grow. The third, only with the page requested: for page 100, with OFFSET, it also reads the previous 4,950, joins the lookup tables to all of them and touches 30,000 rows. Paginating by the last date and id seen, with the id also in the ORDER BY, instead of skipping rows, avoids reading them.

What I would do from day one

Identify the critical queries and their access pattern: what they filter, what they join, in what order they return, how they paginate and at what volume.
Design indexes around how the data is retrieved, not only around the relationships between tables.
Separate transaction processing from analytical queries and reports, so they do not compete for the same resources.
Measure latency, review plans periodically and test with representative volumes. A query is not optimized because it answered fast in an isolated test.

To read a plan of your own, compare the before and after, or see how the work grows with the table, there is the MySQL plan reader: it runs in the browser, tells what MySQL estimates apart from what it measured, and what you paste never leaves your machine.

The lesson was not that FORCE INDEX and STRAIGHT_JOIN should always be used, but that the execution plan has to be checked against real data, contrasting its estimates with the actual execution.

It was not simply a matter of creating an index. It took understanding how MySQL joined, filtered and sorted the data.

A database does not get slow only because it grows. It can also get slow because our queries keep asking it to do more work than necessary.

Israel Negrete Lepe is an electronics engineer. Twenty-five years making different systems work together under real conditions: from the sensor, the vehicle or the transaction to the backend, the data, security and operations. Today, increasingly, edge AI.