How do LIMIT and OFFSET work together, and what goes wrong when you use them for pagination?
The LIMIT says how many rows to keep, and the OFFSET says how many to skip first.
CREATE TABLE customers (
id INTEGER PRIMARY KEY,
name VARCHAR(120) NOT NULL,
city VARCHAR(80) NOT NULL
);
INSERT INTO customers (id, name, city) VALUES
(1, 'Ada Lovelace', 'London'),
(2, 'Grace Hopper', 'New York'),
(3, 'Alan Turing', 'Manchester'),
(4, 'Katherine Johnson', 'Hampton'),
(5, 'Margaret Hamilton', 'Cambridge');
-- The first three.
SELECT id, name FROM customers ORDER BY id LIMIT 3;
-- The next two. The offset skips the first three.
SELECT id, name FROM customers ORDER BY id LIMIT 2 OFFSET 3;
So what goes wrong when you use them for pagination? Two things, and both of them are about rows moving while somebody is looking at the list.
The first one is that the rows shift. Imagine a list of ten rows and the reader is on the second page, which is rows eleven to twenty. Now somebody inserts two rows at the top. Everything moves down by two, so the second page now starts two rows later than it did, and the reader sees two rows that they already saw. Delete a row and the opposite happens, and two rows get skipped and never seen at all.
The second one is that it gets slower the further you go. To give you the rows from eleven to twenty, the engine has to work out the first ten rows and then throw them away. To give you the rows from a million and one to a million and ten, it has to work out a million rows and throw them all away. The work grows with the depth of the page.
There is a third thing that is worth knowing. If you do not put an ORDER BY in the query, then the order of the rows is not promised, so the pages are not even steady with respect to each other. The OFFSET is counting rows in whatever order the engine happened to produce them.
The next question in this topic looks at the better way to do it.