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

Question

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?

Answer 1

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.

Answer 2

Maybe neither of those is optimal.

A common thing to do in web pages is to "paginate", wherein the first 'page' shows the 'first' 10 items, the second page shows the next 10, etc.

Using OFFSET is terribly inefficient when you get farther and farther into the list -- it must gingerly step over each of "offset" rows before seeing the 10.

If id an AUTOINCREMENT, there is no guarantee that the ids will be consecutive over time. Deletes, REPLACE, INSERT IGNORE, replication in a cluster, etc, can leave gaps. Sure, the number works nicely today. But you should not trust tomorrow after someone "fixes" something in the data.

The optimal method for paginating is "remembering where you left off". No OFFSET is used. id (or some other unique column(s)) is merely a place-holder, not a number.

More details: http://mysql.rjweb.org/doc.php/pagination

OP's 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?

(Let me modify the numbers to make the Answer more obvious:)

• limit 10 offset 300 -- The processing must touch 300 rows before getting to the desired 10; that's 310 rows touched.
• where id >= 300 and id < 310 -- Assuming there is an index on id (probably the PRIMARY KEY), only 10 rows need to be touched.
• As I point out in my link, this avoids an unmentioned issue: What if the row with id=305 were deleted? The range approach would get only 9 rows. So... WHERE id >=300 ORDER BY id LIMIT 10 gets you exactly 10 and cannot be fooled by missing ids.
• Even better: Use LIMIT 11. This wastes a little in that it grabs an extra row. But it lets you know whether to include a [Next] button on the page. If you get 11 rows back, there is a "next" page. If <=10 rows, there is not. This is a small price to make your UI more user friendly; you want that, don't you?