🔴 Indexes & Views

Indexes & Views: Think of an index as the index at the back of a book: instead of scanning all 500 pages you look it up — "checkout → page 287".

Think of an index as the index at the back of a book: instead of scanning all 500 pages you look it up — "checkout → page 287". A VIEW is a different animal altogether: not the book, but a **bookmark** on a section you keep returning to. It duplicates no content; it only stores the shortcut to getting there. So if indexes are that good, why doesn't the database just index every column automatically? The answer is in the price: an index speeds up reads and **slows down writes**. Every INSERT/UPDATE must now update the table plus every index tree on it — like revising the back-of-book index each time you add a paragraph. Bulk-loading into a table with five indexes is many times slower than into one with none. An index is not a "more is better" dial; it is a read/write trade-off decision. The trade is familiar from Java: `HashMap` crushes `ArrayList` on lookup, but pays for it in memory and in hashing on every `put`. An index is the same idea projected onto disk — there is no free speed, only relocated cost. For QA this has two concrete faces. First: when a setup script inserting 100k rows of test data unexpectedly takes minutes, the index count on the target table is the first thing to suspect. Second and more dangerous: in performance testing, an index that exists on staging but not in production (or the reverse) turns your "40 ms query" into 4 seconds on live — if the index schema of your test environment doesn't match production, the performance you measured isn't real. VIEWs, meanwhile, collapse that recurring 6-table JOIN in QA reports into a single name everyone shares; most "my report says 12 bugs, yours says 9" arguments come from two people writing the JOIN differently.

Indexes — Speed Up Queries

Micro Lab: SQL — GROUP BY / CTE / Window

Replace the TODO line with the critical line from the expected solution. This is not a real runtime; the goal is to reinforce writing the correct structure in a controlled way.

What Does CREATE INDEX Actually Build on Disk?

When CREATE INDEX runs…

When CREATE INDEX runs, the database reads EVERY existing value in that column (e.g. status) and BUILDS a separate, sorted B-Tree structure on disk — on large tables this can take seconds or minutes.

AFTER the index exists…

AFTER the index exists, every INSERT/UPDATE/DELETE UPDATES both the base table and this B-Tree — this is why too many indexes slow down writes.

EXPLAIN SELECT ... does NOT run the query…

When EXPLAIN SELECT ... runs, the database does NOT execute the query — it just SHOWS whether it plans "type: ALL" (scan the whole table) or "type: ref" (use the index).

Before the index, if EXPLAIN says "ALL"…

Before the index, if EXPLAIN says "ALL", the database checks EVERY row ONE BY ONE to find status='FAIL' matches — this time grows LINEARLY as the table grows.

After the index, the same EXPLAIN…