Why a Simple SELECT LIMIT 1 Can Be Terribly Slow in MySQL InnoDB

Background

Monitoring showed many slow‑query alerts and the business reported slow responses; the investigation began by opening a client to confirm the cause.

Observed Symptoms

All slow queries target the table sbtest1.

Most queries select the latest row, e.g., SELECT * FROM sbtest1 LIMIT 1.

The rows examined count is 1, indicating no large data scans.

Problem Analysis

The SQL itself shows no issue, and host/network I/O is normal. Profiling shows that the query spends most of its time in the Sending data state (≈0.945 s, about 99% of execution time).

The thread is reading and processing rows for a SELECT statement, and sending data to the client. Because operations occurring during this state tend to perform large amounts of disk access (reads), it is often the longest‑running state over the lifetime of a given query.

"Sending data" means reading and processing rows and sending them to the client; with only one row, the time is spent on reading and processing that row.

Further investigation revealed that the InnoDB metric History list length kept increasing to tens of thousands, indicating many UNDO records not yet purged. In REPEATABLE READ isolation, long‑running transactions keep old versions visible.

MVCC Overview

InnoDB implements Multi‑Version Concurrency Control (MVCC), allowing non‑locking SELECTs. Each row stores three system columns: DATA_ROW_ID: generated ROW_ID if no explicit primary key, otherwise the user‑defined primary key value. DATA_TRX_ID: transaction ID that created or deleted the row. DATA_ROLL_PTR: pointer to the rollback segment containing the previous version.

Read View

A read view is created before a SELECT executes. In READ COMMITTED isolation each SELECT gets its own view; in REPEATABLE READ the first SELECT of a transaction creates the view and all subsequent SELECTs reuse it.

The read view contains three important variables: low_limit_id: the highest active transaction ID at view creation. up_limit_id: the lowest active transaction ID at view creation. trx_ids: list of all active transaction IDs at view creation.

Record Visibility Rules

If DATA_TRX_ID < up_limit_id, the row was committed before the view and is visible.

If DATA_TRX_ID equals the transaction’s own ID, the row is visible.

If DATA_TRX_ID > up_limit_id, the row was created after the view and is invisible.

In REPEATABLE READ, a row whose DATA_TRX_ID appears in the view’s trx_ids list is also invisible.

Rollback Pointer

UNDO logs store previous versions. DATA_ROLL_PTR points to the latest UNDO record; each UNDO record points to its predecessor, forming a chain that can be traversed to reconstruct older versions.

Reproducing the Slow Query

Test environment with three sessions:

Session 1 runs a long transaction without committing.

Session 2 deletes about 20 million historical rows.

Session 3 executes the problematic query.

Query SELECT * FROM sbtest1 LIMIT 1 is slow because InnoDB must scan many deleted rows before finding a visible one.

Query SELECT * FROM sbtest1 ORDER BY id DESC LIMIT 1 is fast because scanning starts from the highest ID where rows are not deleted.

Execution Details

For the LIMIT 1 query, InnoDB scans rows sequentially, applying MVCC visibility checks. Deleted rows remain in the index; each check involves comparing DATA_TRX_ID with the current transaction ID and the read view limits.

Row 1: visible according to rule 1, but marked DELETED → invisible.

Row 2: same as above.

Continue until a visible, non‑deleted row is found or the table is exhausted.

Because 20 million rows are marked DELETED, the scan is costly.

Conclusion

Large numbers of deleted rows within the scan range dramatically degrade query performance; secondary‑index scans suffer similarly.

Long‑running transactions keep UNDO records alive; in high‑load OLTP systems this can become a disaster.

Monitor and kill long transactions, and keep automatic commit enabled when possible.

In REPEATABLE READ isolation, UNDO records are retained for older transactions; in READ COMMITTED they are purged quickly, avoiding the issue described.