technotes · topic

MySQL

First-principles depth — the clustered index, query planning, transactions, locking & replication.

GlossaryContext: InnoDB, MySQL 8.0+

Lessons

1. 0001 The clustered index

   The table is a B+tree by primary key — so a secondary read is a double lookup, and PK choice is a storage decision.

   done
2. 0002 Composite indexes & the leftmost-prefix rule

   A composite index is a phone book — only leftmost prefixes are searchable, so column order is a design decision.

   done
3. 0003 Reading EXPLAIN

   Grade any plan in one pass: access type, key, rows × filtered, Extra — plus EXPLAIN ANALYZE for estimates vs reality.

   done
4. 0004 When indexes are ignored

   Functions on columns, leading wildcards, type mismatches, low selectivity — why a perfect index still gets skipped.

   planned

Reference