Lesson 6 / الدرس 6

What an index actually is / ما الفهرس فعلًا

An index is a second copy of one or two columns, kept in order, with a pointer back to the row. Everything an index can and cannot do follows from the words "kept in order", and reasoning from those three words gets you further than memorising rules.

الفهرس نسخة ثانية من عمود أو عمودين، محفوظة مرتّبة، ومعها مؤشر إلى الصف. وكل ما يستطيعه الفهرس وما لا يستطيعه يتبع كلمتَي «محفوظة مرتّبة»، والاستدلال من تلك الكلمات يبلغ بك أبعد من حفظ القواعد.

Think of the index at the back of a book. It is not the book; it is a sorted list of terms with page numbers. You use it by knowing the beginning of the word you want — which is why you can find "normalisation" in a second and cannot find "every word ending in -ation" at all. A database index works the same way, for the same reason.

CREATE INDEX bookings_guest ON bookings (guest_id);

-- What now exists, conceptually: guest_id in order, each pointing back.
--
--   guest_id | row
--   ---------+-----
--         12 | ->
--         12 | ->
--         41 | ->
--         42 | ->      the database jumps straight here
--         42 | ->      reads while the value still matches
--         57 | ->      and stops

-- Which is why this is now fast:
SELECT * FROM bookings WHERE guest_id = 42;

-- And why this is too. A sorted list gives you ranges for free.
SELECT * FROM bookings WHERE guest_id BETWEEN 40 AND 50;

The work went from "read five million rows" to "jump to a position and read three". Ranges come free because the values are in order — once you are at 40, everything up to 50 is sitting next to it. The same property is why ORDER BY guest_id can also use this index instead of sorting the results afterwards.

انتقل العمل من «اقرأ خمسة ملايين صف» إلى «اقفز إلى موضع واقرأ ثلاثة». والمديات تأتي مجانًا لأن القيم مرتّبة — فحالما تكون عند 40، يكون كل ما حتى 50 جالسًا بجواره. والخاصية نفسها هي سبب قدرة ORDER BY guest_id على استخدام هذا الفهرس بدل ترتيب النتائج بعدها.

Two columns, and why the order of them matters

CREATE INDEX bookings_guest_date ON bookings (guest_id, starts_on);

-- Sorted by guest_id first, then by starts_on within each guest — exactly
-- like a phone book sorted by surname, then first name.

SELECT * FROM bookings WHERE guest_id = 42;                      -- uses it
SELECT * FROM bookings WHERE guest_id = 42 AND starts_on > '..'; -- uses it
SELECT * FROM bookings WHERE starts_on > '2026-01-01';           -- CANNOT

-- The last one is the phone book question: find everyone called Sara,
-- when the book is ordered by surname. You are back to reading all of it.

A composite index can be used from the left, and only from the left. That single rule decides most of what you need to know about them — including that an index on (guest_id, starts_on) makes a separate index on guest_id redundant, and does nothing at all for a query about dates alone.

الفهرس المركّب يمكن استخدامه من اليسار، ومن اليسار فقط. وتلك القاعدة وحدها تقرر معظم ما تحتاج معرفته عنها — ومنه أن فهرسًا على (guest_id, starts_on) يجعل فهرسًا منفصلًا على guest_id زائدًا، ولا يفعل شيئًا البتة لاستعلام عن التواريخ وحدها.

The one that surprises people

If an index holds every column a query asks for, the database can answer from the index alone and never touch the table. That is a covering index, and it is often the difference between fast and instant — the second read, the one that fetches the actual row, is usually the expensive half. It is also the honest argument against SELECT *: asking for columns you do not need is what stops the index covering you.

إن حمل الفهرس كل عمود يطلبه الاستعلام، استطاعت قاعدة البيانات الإجابة من الفهرس وحده ولم تمسّ الجدول قط. وذلك فهرس مغطٍّ، وهو الفرق بين السريع والفوري غالبًا — فالقراءة الثانية، التي تجلب الصف الفعلي، هي النصف الباهظ عادةً. وهو أيضًا الحجة الصادقة ضد SELECT *: فطلب أعمدة لا تحتاجها هو ما يمنع الفهرس من تغطيتك.

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

1. Why can an index not answer WHERE name LIKE '%hassan'?

2. You have an index on (guest_id, starts_on). Which query cannot use it?

3. Why might the database ignore an index on a status column with two values?