Why Adding LIMIT 1 Can Slow Down MySQL Queries 50× and How to Fix It

When writing SQL, many developers add LIMIT 1 to fetch a single row, assuming it speeds up the query. In a real‑world case, adding LIMIT 1 made the query 50 times slower.

1. Reproduce the scenario

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

idx_user_status ( (user_id, status)) – used for filtering by user and status.

idx_create_time ( (create_time)) – used for ordering by time.

The original query that triggered the problem:

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

Running this query generated a slow‑query alert, taking about 2.5 seconds. Removing LIMIT 1 reduced the execution time to roughly 50 ms.

2. Explain analysis

Using EXPLAIN revealed the optimizer's choices:

Without LIMIT: MySQL used idx_user_status, quickly filtered the few matching rows and performed an in‑memory sort – fast because the data set was small.

With LIMIT: MySQL switched to idx_create_time, scanning the table in descending create_time order and checking each row for the user and status. The matching row was over a year old, so MySQL scanned more than 2 million rows before finding it, effectively turning the query into a near full‑table scan.

The optimizer "guessed" that it would encounter a matching row early, a bet that failed due to highly skewed data distribution.

3. Solutions

Method 1: FORCE INDEX

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

This forces the optimizer to use the filtering index, but ties the query to a specific index name.

Method 2: Create a covering 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 row and return it already sorted, eliminating the extra sort step.

Method 3: Subquery trick

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

The inner query uses the filtering index; the outer LIMIT 1 does not influence the inner index choice, effectively separating the limit from the index selection.

Conclusion

While LIMIT 1 is generally a good habit, it can backfire when the optimizer mis‑estimates row distribution. In such cases, examine the execution plan with EXPLAIN and consider forcing the appropriate index, adding a composite index, or rewriting the query with a subquery.