Deep Offsets That Slow Every Page

A database cannot jump to the hundred-thousandth row of a sorted result: to skip rows with an offset, it reads them and throws them away. The deeper the page, the more rows it reads for nothing, so the cost of a page grows with its number, while a cursor query reads the same few rows on every page.

The symptom. An export job walks every page of the orders table. The first pages return at once; by the end each page takes hundreds of times longer, and the job takes hours.

The trigger. The export uses ORDER BY id LIMIT 20 OFFSET ?, adding 20 to the offset for each page, on a table that has grown to a million rows.

The mechanism. The offset query's time grows in step with the offset: ten times the offset, about ten times the time. The index on id gives the database the rows in order, but it still steps through every one before the offset. The cursor query, WHERE id > ? ORDER BY id LIMIT 20, goes straight to its starting key in the index, so its time does not move.

The cost. Walking all 50,000 pages with offsets reads about 25 billion rows in total, against one million with a cursor. On a disk-based database under load, with wider rows, each deep page costs far more than these in-memory figures.

The remedy. Use cursors for anything that walks deep: exports, syncs, infinite scroll. Where page numbers must stay, cap how deep a reader can go, as many search engines do, or narrow the result with filters first.

So treat an offset as a cost the database pays in rows read, and use a cursor wherever pages run deep.