Re: Full Table Scans When LIMIT / OFFSET Are Used

[Carlos Mennens]

WITH LIMIT OFFSET:

 -> ORDER BY `t`.`third_party_reference_key`
    -> LIMIT 18446744073709551610 OFFSET 0;
+----+-------------+-------+------------+-------+---------------+-----------------------------------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type  | possible_keys | key                               | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------+------------+-------+---------------+-----------------------------------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | t     | NULL       | index | reference_key | third_party_reference_key_version | 148     | NULL |    0 |     0.00 | Using where |
+----+-------------+-------+------------+-------+---------------+-----------------------------------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.05 sec)

WITHOUT

-> ORDER BY `t`.`third_party_reference_key`;
+----+-------------+-------+------------+------+---------------+---------------+---------+-------------+------+----------+---------------------------------------+
| id | select_type | table | partitions | type | possible_keys | key           | key_len | ref         | rows | filtered | Extra                                 |
+----+-------------+-------+------------+------+---------------+---------------+---------+-------------+------+----------+---------------------------------------+
|  1 | SIMPLE      | t     | NULL       | ref  | reference_key | reference_key | 153     | const,const |    1 |   100.00 | Using index condition; Using filesort |
+----+-------------+-------+------------+------+---------------+---------------+---------+-------------+------+----------+---------------------------------------+
1 row in set, 1 warning (0.05 sec)