Lesson 8 / الدرس 8

Every index is paid for on every write / كل فهرس يُدفع ثمنه عند كل كتابة

Indexes are presented as free speed and are not. Each one is another sorted structure that has to be updated whenever a row is inserted, changed or removed — so a table with nine indexes does ten writes for every one you asked for.

تُقدَّم الفهارس سرعةً مجانية وليست كذلك. فكل واحد بنية مرتّبة أخرى يجب تحديثها كلما أُدخل صف أو غُيّر أو أُزيل — فالجدول بتسعة فهارس يؤدي عشر كتابات لكل واحدة طلبتها.

The reads got faster and something had to pay for it. An INSERT into a table with nine indexes writes the row once and updates nine sorted structures, each of which may have to shuffle its contents to keep them in order. The same is true of every UPDATE that touches an indexed column.

صارت القراءات أسرع وكان لا بد لشيء أن يدفع الثمن. فـINSERT في جدول بتسعة فهارس تكتب الصف مرة وتحدّث تسع بنى مرتّبة، وقد يضطر كلٌّ منها إلى إعادة ترتيب محتوياته ليبقى مرتّبًا. والأمر نفسه في كل UPDATE تمسّ عمودًا مفهرسًا.

Where indexes come from, and why nobody removes them

They accumulate. A page was slow, somebody added an index, the page got quicker and the ticket closed. Nobody wrote down which query it was for. Two years later the table has eleven indexes, three of them cover the same leading column, and nobody will remove any of them because nobody can prove they are unused.

-- Three indexes that one would have covered.
CREATE INDEX i1 ON bookings (guest_id);
CREATE INDEX i2 ON bookings (guest_id, starts_on);
CREATE INDEX i3 ON bookings (guest_id, starts_on, status);

-- i3 alone can serve every query the other two can, because a composite
-- index is usable from the left. i1 and i2 are pure cost: storage on
-- disk, memory that could have cached something else, and two extra
-- structures to update on every single write.

DROP INDEX i1 ON bookings;
DROP INDEX i2 ON bookings;

-- Most databases can tell you which indexes have actually been used
-- since they last restarted. Ask before you guess:
--   MySQL       sys.schema_unused_indexes
--   PostgreSQL  pg_stat_user_indexes, where idx_scan = 0

The redundancy in that first block is extremely common and completely invisible until somebody lists the indexes on a table and reads them together. A leftmost prefix of another index is dead weight — and the usage views in the last comment turn "I think this is unused" into a fact you can act on.

Where the cost actually bites

Bulk loads. Importing a million rows into a heavily indexed table is dramatically slower than importing them and building the indexes afterwards. If an import is taking hours, this is usually why.
Memory. Databases keep hot indexes in memory. Every unused index competing for that space is evicting something a real query wanted, which makes queries slower in a way that never points back at the index causing it.
Write-heavy tables. A log or events table takes far more writes than reads. Indexing it the way you would index a table people search is paying a large bill for a small benefit.

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

1. You have indexes on (guest_id), (guest_id, starts_on) and (guest_id, starts_on, status). What is true?

2. Why is a million-row import faster if you build the indexes afterwards?

3. Why does nobody remove unused indexes?