Why Is This Empty MySQL Table Taking 20 Seconds to Query? dropdown to pick auto / light / dark.<br>gives a no-JS open/close for free; the script below<br>wires selection, persistence, and closing. Icons + text labels.<br>Sits at the far right of the bar, past the nav links. --> Auto<br>Light<br>Dark

SELECT * FROM my_table;

Query execution time: ~20 seconds

Table rows: 0

This made absolutely no sense.

A few years ago, I found myself staring at a performance metric that defied logic. We had a table that was, for all intents and purposes, empty . Yet, querying it was taking longer than a coffee break.

The Setup

Here’s the context: one massive MySQL database attached to an old monolith. Multiple teams outside of monolith had read-only access. The database was running on one of the beefiest RDS instances available - typically at 50-60% load.

What bothered me was that this load level never quite matched our actual traffic patterns. Something felt off.

Our monolith used the transactional outbox pattern - storing records in MySQL before publishing to RabbitMQ. The setup was straightforward: the monolith writes messages to an outbox_message table, and a separate worker service reads them in batches, publishes to RabbitMQ, then deletes them.

High throughput table. Lots of writes. Lots of deletes. Should be simple.

Then these warnings started appearing:

[WARN] Slow batch processing detected: 23.4s<br>[WARN] Slow batch processing detected: 31.2s<br>[WARN] Slow batch processing detected: 47.8s<br>Confirming the Symptoms

The monitoring dashboard showed the first signs. The outbox worker’s latency metric - which measures the time to fetch messages from the database and publish them to RabbitMQ - had jumped to high levels. We were seeing 20+ seconds with spikes up to 50 seconds.

Distributed traces pointed to the culprit: the SELECT query that fetches message batches was taking almost all the time. The commit operation was also suspiciously slow.

Time to verify directly:

SELECT * FROM outbox_message LIMIT 1;<br>An empty table taking 20 seconds to query.

I checked RDS Performance Insights expecting to see obvious problems - CPU maxed out, disk I/O bottlenecks, something.

Nothing. The database was sitting at a steady 50-60% load. It looked “busy,” but not “breaking.” No unusual spikes. The Top Queries view didn’t even show our problematic query.

Which was actually the first real clue. This wasn’t a typical bottleneck.

Following the Clues

The MySQL slow query log revealed what Performance Insights had missed:

# Query_time: 18.814028 Lock_time: 0.000021 Rows_sent: 1000 Rows_examined: 1000<br>SELECT * FROM outbox_message LIMIT 1000 FOR UPDATE SKIP LOCKED;<br>18.8 seconds for a query that examined only 1000 rows. The lock time was negligible.

My first theory: blocking transactions. The worker retrieves messages in batches concurrently, so maybe they’re stepping on each other.

SELECT trx_id, trx_state, trx_started, trx_query,<br>dl.lock_type, dl.lock_mode, dl.LOCK_STATUS<br>FROM information_schema.innodb_trx<br>JOIN performance_schema.data_locks dl ON trx_id = ENGINE_TRANSACTION_ID<br>WHERE trx_query LIKE '%outbox_message%'<br>ORDER BY trx_started DESC;<br>Yes - several long-running transactions on the outbox_message table. But here’s the thing: no deadlocks. All transactions had GRANTED locks, and all lock types were compatible. No conflicts.

The locks weren’t the problem.

Let’s look at the table itself:

SELECT table_name,<br>sys.format_bytes(data_length) AS data_size,<br>sys.format_bytes(data_free) AS free_space,<br>TABLE_ROWS<br>FROM information_schema.tables<br>WHERE TABLE_NAME='outbox_message';<br>data_size: 1.2 GiB<br>free_space: 1.1 GiB<br>TABLE_ROWS: 0<br>An empty table occupying 1.2 GiB on disk. Something was fundamentally wrong with this particular table.

Time to look deeper:

SHOW ENGINE INNODB STATUS;<br>And there it was:

History list length 21,842,680<br>21 million entries in the history list.

Understanding What’s Actually Happening

Before we go further, let’s step back and understand what this history list means.

Multi-Version Concurrency Control (MVCC)

InnoDB uses MVCC to allow concurrent reads and writes without locking. When you update or delete a row:

InnoDB doesn’t immediately overwrite or remove the old version

It creates a new version of the row (or marks it as deleted)

The old version gets stored in the undo log

A reference to this old version is added to the history list

This enables readers to access old versions while writers work on new ones - no blocking required.

InnoDB’s purge operations run in the background to clean up old row versions. But here’s the critical constraint: the purge thread can only remove versions that are older than currently active transactions .

When you start a transaction, InnoDB creates a “snapshot” at that point in time. Even if your transaction sits idle, MySQL must keep all row versions created after your transaction started - because you might need them for consistent reads.

The Domino Effect

Here’s what happened in...