Indexes and query plans

Vocabulaire

English	Sens
B-tree	B-tree
query plan	query plan

English

The sorted shortcut

A B-tree index keeps keys sorted in a balanced multiway tree: our order-4 teaching tree holds 15 keys in 2 levels — any key in 2 node reads against 15 sequential.
The query plan is the engine's chosen pipeline: EXPLAIN QUERY PLAN prints it, and the plan — not the query text — prices the work.

{exercise:uni-cs0804-btree}

Pratique

A fan-out-100 B-tree of height 3 covers — keys

Difficulté 1/5 · 100 pièces + un petit bonus lors de la première réussite aujourd'hui

English

A node of $k$ keys routes among $k + 1$ children: fan-out $\approx m$ covers $m^h$ entries at height $h$. Fan-out 100: three levels cover $100^3 = 10^6$ keys — a million-row lookup in 3 reads, for every key (splits keep it balanced).
SELECT title FROM Course WHERE credits = 20: unindexed the plan reads SCAN Course; with INDEX ON Course(credits) it reads SEARCH Course USING INDEX idx (credits=?). On 6 rows both are instant; on 6 million it is 3 reads against 6 million.

{exercise:uni-cs0804-plan}

Pratique

EXPLAIN QUERY PLAN without an index prints —

Difficulté 1/5 · 100 pièces + un petit bonus lors de la première réussite aujourd'hui

English

Each index adds work to every insert and delete: our schema with 3 indexes writes 4 structures per insert (table + 3 trees). A log table with 5 indexes at 10 million inserts a day maintains 50 million index entries daily.
Index what you query, drop what you do not, and read the plan, not the query. WHERE UPPER(name) = … defeats a plain index — the computed column does not match.

{exercise:uni-cs0804-tax}

Pratique

A table with 3 indexes costs each insert —

Difficulté 1/5 · 100 pièces + un petit bonus lors de la première réussite aujourd'hui