Search code examples
mysqlsqlperformancerangelimit

In SQL, what is faster: LIMIT ... OFFSET or range queries?

Given

  • The following schema:

    CREATE TABLE employees (
    name CHAR,
    PRIMARY KEY id INT
);

  • The table is sorted on id

  • There are 100 unique ids from 1-100 in the table.

Example

| name    | id |
|---------|----|
| Lynne   | 1  |
| Johnny  | 2  |
| D'Andra | 3  |
| Kimmel  | 4  |
|        ...   |

Objective

Get 10 people with ids greater than or equal to 3.

Question

Would it be faster to use select name from employees order by id limit 10 offset 3 or select name from employees where id >= 3 and id <13 order by id, and why?

What I've checked out so far

Does adding 'LIMIT 1' to MySQL queries make them faster when you know there will only be 1 result?: This says that using limit is faster than not using limit but it doesn't compare it to range queries.

Select query with offset limit is too much slow: This says that offset is often slow because it needs to go through all the rows to get to the offset. It doesn't discuss whether it is slower to use offset x than id >= x for any integer x?

Solution

  • Your two queries are not the same. They are the same only when you know that the id column has no gaps and (and no duplicates, but that makes sense for an id).

    For small offsets, there shouldn't be a difference for such a simple query. I do think, though, that MySQL will read all results for the offset query and then start returning results when it hits the offset number. That is, it actually counts the rows and then outputs the one after the offset.

    The where clause should cause MySQL to go directly to the right record in the index. That should be faster for larger result sets.