Mysql Question posted in
The official MySQL documentation can be found here.

Mysql – Query is slow only if LIMIT is greater than rows returned

We are find a specific query running very very slow if the LIMIT value is larger than the number of returned rows. We are running MySQL on RDS – 8.0.mysql_aurora.3.02.0 and have no other performance issues.

Example 1 – query returns exactly 10 rows in 2 seconds:

SELECT `some_table` . *  FROM `some_table` 
WHERE `some_table` . `deleted_at` IS NULL 
 AND `some_table` . `created` = TRUE 
 AND `some_table` . `owner_id` IN (286997, ... , 617727) 
 AND (some_table.activity_date >= '2024-09-01' 
 AND some_table.activity_date <= '2025-08-31') 
ORDER BY `some_table` . `id` DESC 
LIMIT 10 OFFSET 276;

10 rows in set (2.08 sec)

Explain:

| id | select_type | table       | partitions | type  | possible_keys                                                                                                             | key     | key_len | ref  | rows  | filtered | Extra                            |
+----+-------------+-------------+------------+-------+---------------------------------------------------------------------------------------------------------------------------+---------+---------+------+-------+----------+----------------------------------+
|  1 | SIMPLE      | some_table  | NULL       | index | index_some_table_on_owner_id,index_some_table_on_activity_date,index_some_table_on_deleted_at,index_some_table_on_created | PRIMARY | 4       | NULL | 30590 |     0.00 | Using where; Backward index scan |

Explain analyze:

 -> Limit/Offset: 10/276 row(s)  (cost=195.49 rows=0) (actual time=788.859..1407.051 rows=10 loops=1)
    -> Filter: ((some_table.created = true) and (some_table.deleted_at is null) and (some_table.owner_id in (286997, ... , 617727)) and (some_table.activity_date >= DATE'2024-09-01') and (some_table.activity_date <= DATE'2025-08-31'))  (cost=195.49 rows=1) (actual time=1.604..1406.993 rows=286 loops=1)
        -> Index scan on some_table using PRIMARY (reverse)  (cost=195.49 rows=30590) (actual time=0.092..1358.637 rows=219665 loops=1)

Example 2 – query returns 9 rows on over 60 seconds:

SELECT `some_table` . *  FROM `some_table` 
WHERE `some_table` . `deleted_at` IS NULL 
 AND `some_table` . `created` = TRUE 
 AND `some_table` . `owner_id` IN (286997, ... , 617727) 
 AND (some_table.activity_date >= '2024-09-01' 
 AND some_table.activity_date <= '2025-08-31') 
ORDER BY `some_table` . `id` DESC 
LIMIT 10 OFFSET 277;

9 rows in set (1 min 0.74 sec)

Explain:

| id | select_type | table       | partitions | type  | possible_keys                                                                                                             | key     | key_len | ref  | rows  | filtered | Extra                            |
+----+-------------+-------------+------------+-------+---------------------------------------------------------------------------------------------------------------------------+---------+---------+------+-------+----------+----------------------------------+
|  1 | SIMPLE      | some_table  | NULL       | index | index_some_table_on_owner_id,index_some_table_on_activity_date,index_some_table_on_deleted_at,index_some_table_on_created | PRIMARY | 4       | NULL | 30697 |     0.00 | Using where; Backward index scan |

Explain analyze:

 -> Limit/Offset: 10/277 row(s)  (cost=210.44 rows=0) (actual time=514.159..50410.699 rows=9 loops=1)
    -> Filter: ((some_table.created = true) and (some_table.deleted_at is null) and (some_table.owner_id in (286997, ... , 617727)) and (some_table.activity_date >= DATE'2024-09-01') and (some_table.activity_date <= DATE'2025-08-31'))  (cost=210.44 rows=1) (actual time=0.896..50410.650 rows=286 loops=1)
        -> Index scan on some_table using PRIMARY (reverse)  (cost=210.44 rows=30697) (actual time=0.059..49691.395 rows=3153118 loops=1)

As you can see on the second analyze the index scan is scanning all rows (3.1 million). No matter what the values of LIMIT and OFFSET if the query is returning less rows than the LIMIT value the query takes significantly longer. Removing the order by DESC does fix the problem but that is required.

All the columns are indexed and we have tried composite indexes and a descending index on created_at with no luck.

Tags:

2

Answers


  1. 0

    You can see in the possible_keys field of the EXPLAIN, the optimizer considered several of your secondary indexes, but ultimately it didn’t use them.

    The key field shows that it chose a scan on the PRIMARY key (the clustered index). The type: index also indicates this.

    So as it must scan the whole PRIMARY key index, it is not using any index assistance to filter based on your conditions. Therefore it must examine every row, and evaluate the conditions one row at a time.

    It does this to avoid sorting, because the optimizer estimates that it would be more costly to sort. If it chooses any of the secondary indexes, these are not necessarily in primary key order, so it would be forced to sort.

    When doing an index scan, it must report rows: 30697 as an upper bound. You query uses LIMIT, so it might not actually read that many rows. But it the EXPLAIN must choose a worst-case estimate, and it has to assume it will scan through all the rows as it searches for those that satisfy the conditions in the WHERE clause.

    Sometimes the optimizer’s estimates are not ideal. Usually they are better than the alternative, but they’re not infallible. You can use index hints to control this if you want to override the optimizer’s choices.

    So my guess about why your second query takes longer is related to the LIMIT optimization. When MySQL runs a LIMIT query, once it finds N rows to satisfy the limit, it stops examining rows. There’s no need to search any further.

    But if the table does not in fact contain enough matching rows to fill the number of rows requested by the LIMIT, then the query must read all the rows in the table. It can’t know until it reads them all that there are only 9 rows that match the conditions. So it really does read all the way to the end of the table.

    Login or Signup to reply.
  2. 0

    This may help some: INDEX(deleted_at, created, activity_date, owner_id)

    Changing the ORDER BY to this may also help:

    ORDER BY activity_date DESC, id DESC
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search