Search code examples
mysqlquery-optimizationmysql-workbenchlimit

LIMIT In MYSQL uses full index scan and not range scan

I have a table named 'emp' with columns emp_id (Primary Key), name, dob, email, with 5 million rows. The following query uses full index scan.

select emp.emp_id from emp limit 2500000, 100000;

MySQL plan:

enter image description here

enter image description here

why mysql cannot perform range scan and it performs full index scan on 4 548 256 rows?

Solution

  • Think of a telephone book. If I ask you to find a person by name, the nature of the telephone book is a sorted list, so you can find the name pretty quickly.

    But if I ask you to find the 25,000th person in the telephone book, the fact that it is a sorted list does not help you. You must start counting from the start of the book until you reach 25,000.

    The search by name is a lookup, either for a specific name or for a range of names. But the important part is that this is a search by value, not by position of the entry.

    A LIMIT query is not a lookup by value, it's a lookup by position. The index does not help this. To get the results of the query, MySQL must start counting all rows from the first row of the table.

    To make your query a range scan, it must search by value:

    SELECT emp.emp_id FROM emp WHERE emp.emp_id BETWEEN 2500000 AND 2600000;

    But this isn't exactly the same as your LIMIT query, because it is possible that the values in emp_id might have gaps, not using every integer value.