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.
| name | id | |---------|----| | Lynne | 1 | | Johnny | 2 | | D'Andra | 3 | | Kimmel | 4 | | ... |
Get 10 people with ids greater than or equal to 3.
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?
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
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.
where clause should cause MySQL to go directly to the right record in the index. That should be faster for larger result sets.