Lesson 7 / الدرس 7

Ask the database what it is going to do / اسأل قاعدة البيانات ماذا ستفعل

You do not have to guess whether an index is being used. EXPLAIN puts the plan in front of you before the query runs, and three of its columns answer almost every question people spend afternoons speculating about.

لست مضطرًا إلى تخمين أيُستخدم فهرس. فـEXPLAIN تضع الخطة أمامك قبل تشغيل الاستعلام، وثلاثة من أعمدتها تجيب كل سؤال تقريبًا يقضي الناس أصائل يتكهنون فيه.

Put EXPLAIN in front of a SELECT and the database tells you how it intends to answer without answering. This is the difference between an opinion about performance and a fact about it, and it costs nothing to check — the query does not run.

ضع EXPLAIN أمام SELECT فتخبرك قاعدة البيانات كيف تنوي الإجابة دون أن تجيب. وهذا هو الفرق بين رأي في الأداء وحقيقة عنه، ولا يكلّف الفحص شيئًا — إذ لا يعمل الاستعلام.

EXPLAIN SELECT * FROM bookings WHERE guest_id = 42;

-- Before the index:
--   type: ALL          <- read every row
--   key:  NULL         <- no index chosen
--   rows: 4832190      <- how many it expects to examine

-- After CREATE INDEX bookings_guest ON bookings (guest_id):
--   type: ref
--   key:  bookings_guest
--   rows: 3

-- Three columns, and they are the three worth learning:
--   type  HOW it will find rows
--   key   WHICH index it picked, or NULL for none
--   rows  how many it expects to look at, not how many it will return

The rows column is an estimate, and it is the one to read first. A query that returns three rows while expecting to examine five million is the whole problem, stated in one number. You do not need to understand the rest of the output to act on that.

عمود rows تقدير، وهو ما يُقرأ أولًا. فالاستعلام الذي يعيد ثلاثة صفوف وهو يتوقع فحص خمسة ملايين هو المشكلة كلها، مذكورةً في رقم واحد. ولست بحاجة إلى فهم بقية المخرَج للتصرف بناءً عليه.

The access types, worst to best

type	What it means	Verdict
ALL	Read every row in the table	A full scan. Fine on a small table, fatal on a large one
index	Read every entry in an index	Still everything, just a smaller everything
range	Walk part of an index — BETWEEN, >, IN	Good. This is what most filters should be
ref	Jump to matching entries for a value	Good. A normal indexed lookup
const / eq_ref	At most one row, by primary or unique key	The best there is

ALL on a table of any size is the finding. Everything else is a matter of degree, and chasing range up to ref is rarely worth the effort — whereas turning ALL into anything at all usually changes the page from slow to instant.

ALL على جدول من أي حجم هي النتيجة. وكل ما عداها مسألةُ درجة، ومطاردة range صعودًا إلى ref نادرًا ما تستحق الجهد — بينما تحويل ALL إلى أي شيء البتة يحوّل الصفحة عادةً من بطيئة إلى فورية.

Two lines in the Extra column that matter

Using filesort — the database could not get the rows in the order you asked for, so it fetched them and sorted them afterwards. On a large result that is memory and time. An index that already has them in that order removes it.
Using index — the good one, and easy to confuse with the index access type above. It means every column needed was in the index, so the table was never touched: the covering index from the last lesson, confirmed.
Using temporary — a working table was built to answer, usually for a GROUP BY that no index supports. Worth knowing about, rarely the first thing to fix.

ولمعظم قواعد البيانات أيضًا EXPLAIN ANALYZE، التي تشغّل الاستعلام وتبلّغ بما حدث فعلًا بجوار ما تنبّأت به. وحين يختلف التقدير والواقع اختلافًا شديدًا، تكون الإحصاءات متقادمة — وذلك، لا الاستعلام، ما يحتاج الانتباه.

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

1. Which single number in an EXPLAIN output is worth reading first?

2. EXPLAIN reports type: ALL. What does that mean?

3. Why is a plan from your development database not enough?