Lesson 13 / الدرس 13

Page 900 is slower than page 1 / الصفحة 900 أبطأ من الصفحة 1

OFFSET is the pagination everybody writes and it gets slower the further in you go, because the database has to produce and discard every row it skips. There is another way to ask the same question that does not.

OFFSET هو الترقيم الذي يكتبه الجميع ويبطؤ كلما توغّلت، لأن على قاعدة البيانات إنتاج كل صف تتخطاه ثم رميه. وثمة طريقة أخرى لطرح السؤال نفسه لا تفعل ذلك.

LIMIT 20 OFFSET 18000 does not jump to row 18,000. It produces eighteen thousand rows in order, throws all of them away, and returns the next twenty. Page one is instant, page nine hundred is a full scan with extra steps, and nothing about the query looks different.

LIMIT 20 OFFSET 18000 لا تقفز إلى الصف 18000. بل تنتج ثمانية عشر ألف صف بالترتيب، وترمي بها كلها، وتعيد العشرين التالية. فالصفحة الأولى فورية، والصفحة التسعمئة مسحٌ كامل بخطوات إضافية، ولا شيء في الاستعلام يبدو مختلفًا.

The same three pages, asked two ways

-- Page 1 and page 2, the usual way. Watch the OFFSET grow.
SELECT id, name FROM guests ORDER BY id LIMIT 2;
SELECT id, name FROM guests ORDER BY id LIMIT 2 OFFSET 2;
SELECT id, name FROM guests ORDER BY id LIMIT 2 OFFSET 4;

-- The same three pages, asked the other way: carry the last id you saw
-- and ask for what comes after it. No OFFSET anywhere.
SELECT id, name FROM guests WHERE id > 0 ORDER BY id LIMIT 2;
SELECT id, name FROM guests WHERE id > 2 ORDER BY id LIMIT 2;
SELECT id, name FROM guests WHERE id > 4 ORDER BY id LIMIT 2;

Both sets return the same three pages. The difference is that the keyset version starts every page by jumping into the index at a value, so page nine hundred costs exactly what page one costs. Six rows is too few to feel it — the point here is that the results are identical, so the change is safe to make.

What you give up

Jumping to an arbitrary page. Keyset gives you next and previous, not "page 47" — because it does not know where page 47 starts without counting. If your interface has numbered pages, this is the conversation to have.
A total page count, for the same reason and the reason the full-scan lesson gave: counting everything is itself a full scan.
Sorting by anything you like. The column you page by has to be indexed and has to break ties — two rows with the same date need a second column, usually the id, or a row gets shown twice or skipped.

Try it live / جرّب بنفسك

<h3>OFFSET</h3>
<div id="a"></div>
<h3>Keyset</h3>
<div id="b"></div>
<h3>Scratch</h3>
<div id="try"></div>
<script>
  sqlTable('#a', "SELECT id, name FROM guests ORDER BY id LIMIT 2 OFFSET 2");
  sqlTable('#b', "SELECT id, name FROM guests WHERE id > 2 ORDER BY id LIMIT 2");
  sqlConsole('#try', 'SELECT id, check_in FROM bookings ORDER BY check_in, id;');
</script>
<style>
  body { font-family: system-ui, sans-serif; padding: 1rem; }
  h3 { font-size: .85rem; color: #64748b; margin: 1rem 0 .3rem; }
</style>

Preview / المعاينة

Check yourself / اختبر نفسك

1. What does LIMIT 20 OFFSET 18000 actually make the database do?

2. Besides speed, what else is wrong with OFFSET on data that is changing?

3. You page by check_in and two bookings share a date. What goes wrong?

Your task / مهمتك

Take a list in something you have built and page through it both ways. Write the OFFSET version and the keyset version of the same three pages, confirm they return identical rows, then say what your interface would have to give up to switch — and whether it can.

خذ قائمة في شيء بنيته ورقّمها بالطريقتين. اكتب نسخة OFFSET ونسخة المفتاح للصفحات الثلاث نفسها، وتأكد أنهما تعيدان الصفوف نفسها، ثم قل عمّ ستتخلى واجهتك للتحويل — وأتستطيع.

The same three pages, written both ways, returning the same rows الصفحات الثلاث نفسها، مكتوبةً بالطريقتين، تعيد الصفوف نفسها
The column you page by named, with the tie-breaker it needs العمود الذي ترقّم به مسمًّى، ومعه فاضّ التعادل الذي يحتاجه
What the interface gives up, and whether that is acceptable here عمّ تتخلى الواجهة، وأمقبول ذلك هنا