Why Adding LIMIT 1 Can Make MySQL Queries 50× Slower—and How to Fix It

When writing SQL, many developers habitually add LIMIT 1 to queries that only need a single row, assuming it will reduce I/O and CPU. However, a recent production incident showed that adding LIMIT 1 made a query 50 times slower.

Reproducing the Issue

The business requirement was to retrieve the most recent "processing" order for a specific user. The orders table contains about 5 million rows and has two key indexes:

idx_user_status ( (user_id, status)) – filters by user and status.

idx_create_time ( (create_time)) – supports ordering by time.

The original SQL was:

SELECT id, order_no, amount
FROM orders
WHERE user_id = 10086 AND status = 1
ORDER BY create_time DESC
LIMIT 1;

Deployed to production, this query triggered a slow‑query alert and took 2.5 seconds . Removing the LIMIT 1 and running the same query without it reduced the execution time to about 50 ms .

Analyzing with EXPLAIN

Running EXPLAIN on both statements revealed the optimizer’s choices:

Without LIMIT: MySQL used idx_user_status, quickly filtering the few dozen rows that match the user and status, then sorting them in memory.

With LIMIT 1: MySQL abandoned the filtering index and chose idx_create_time, scanning the table in descending order of create_time and checking each row for the user and status condition.

The optimizer assumed it would find a matching row early in the time‑ordered scan, but because the target user’s latest qualifying order was over a year old, it had to scan more than 2 million rows before finding it.

Why the Optimizer Made the Wrong Choice

The optimizer evaluates two abstract plans:

Plan A – Filter Index First: Use idx_user_status to locate all matching rows, then sort them. This is efficient when many rows match the filter.

Plan B – Time Index + LIMIT: Use idx_create_time to read rows in the required order and stop after the first match. This avoids a separate sort but relies on the assumption that a matching row appears early.

In this case, the optimizer “bet” that Plan B would find a match quickly, but the data distribution (a sparse recent match) caused the bet to fail, resulting in a full‑table‑scan‑level slowdown.

How to Fix the Problem

Three practical solutions are presented:

1. Force the Desired Index

Use FORCE INDEX (idx_user_status) to tell MySQL to use the filtering index regardless of the LIMIT:

SELECT ... FROM orders FORCE INDEX (idx_user_status) ...;

This immediately restores the fast execution path, though it ties the query to a specific index name.

2. Create a Composite Index

Build an index that satisfies both the filter and the ordering: (user_id, status, create_time) With this index, MySQL can locate the relevant rows and retrieve them already sorted, eliminating the need for a separate sort and avoiding the optimizer’s mis‑choice.

3. Use a Subquery to Isolate LIMIT

If changing the schema is not possible, rewrite the query so that the inner subquery uses the filtering index, and the outer query applies LIMIT 1:

SELECT * FROM (
  SELECT ... FROM orders
  WHERE user_id = 10086 AND status = 1
  ORDER BY create_time DESC
) AS tmp
LIMIT 1;

This prevents the LIMIT clause from influencing the inner index selection.

Takeaway

While LIMIT 1 is generally a good habit, it can backfire when the optimizer’s cost model mis‑estimates data distribution. Whenever a query with LIMIT becomes unexpectedly slow, run EXPLAIN to inspect the chosen plan and consider forcing an index, adding a composite index, or restructuring the query.