Reference · Glossary
Glossary
Canonical definitions for this course. When a term is defined here, lessons use it exactly this way.
Living documentGrows with each lessonContext: InnoDB, MySQL 8.0+
- clustered index
- The B+tree that is an InnoDB table: it stores the full row
data in its leaf nodes, ordered by the primary key. There is exactly one per
table — "the table" and "the clustered index" are the same object. Reaching a
leaf yields the whole row, no further lookup needed.
— intro'd in lesson 0001
- primary key (as clustered index)
- In InnoDB the
PRIMARY KEY is not just a uniqueness constraint —
it defines the physical order of the table, because it is the
clustered index. This is why PK choice is a storage-layout decision, not a
cosmetic one.
— intro'd in lesson 0001
- secondary index
- Any index that is not the clustered index. Each of its leaf records holds the
indexed column(s) plus the primary key columns — the PK acts
as the "pointer" to the row, rather than a physical address.
— intro'd in lesson 0001
- double lookup (bookmark lookup)
- The two-step read a non-covering secondary index forces: first walk the
secondary index to find the matching PK value, then walk the clustered index by
that PK to fetch the rest of the row. Two B+tree descents for one row.
— intro'd in lesson 0001
- covering index
- A secondary index that already contains every column a query needs
(in its key columns + the PK it carries), so the query is answered from the
index alone — the second lookup into the clustered index is skipped. Shows as
Using index in EXPLAIN.
— intro'd in lesson 0001
- B+tree
- The balanced, high-fan-out tree behind every InnoDB index. Internal nodes hold
only keys to route the search; all data (or PK pointers) live in the leaf level,
which is linked for range scans. Depth stays small (typically 3–4 levels even
for millions of rows), so any lookup is a handful of page reads.
— intro'd in lesson 0001
- GEN_CLUST_INDEX (hidden clustered index)
- What InnoDB creates when a table has no
PRIMARY KEY and no
suitable UNIQUE NOT NULL index: a hidden clustered index on a
synthetic 6-byte, monotonically increasing row ID you cannot query or reuse.
A reason to always define an explicit PK.
— intro'd in lesson 0001
- page (16 KB)
- The fixed-size unit InnoDB reads, writes, and caches — 16 KB by default. A
B+tree node is one page; the buffer pool caches pages. Index efficiency is
really "how few pages must I touch to answer this query?".
— intro'd in lesson 0001
- index-organized table
- The general term for a table stored inside its primary-key index
(rather than in a separate heap with indexes pointing at it). InnoDB tables are
always index-organized; some other engines/databases use heap storage instead.
— intro'd in lesson 0001
- composite (multiple-column) index
- A single index built on two or more columns, e.g.
(last_name, first_name).
Its entries are sorted by the first column, then the second within that, and so on —
like a phone book. One composite index serves lookups on every leftmost prefix
of its columns.
— intro'd in lesson 0002
- leftmost-prefix rule
- An index on
(c1, c2, c3) can seek only on (c1),
(c1, c2), or (c1, c2, c3) — any prefix starting at the
leftmost column. A filter that skips the leading column (e.g. on c2
alone) cannot use the index for lookup. The rule that makes column order a design
decision.
— intro'd in lesson 0002
- range condition (stops the prefix)
- A non-equality filter —
>, <, BETWEEN,
LIKE 'x%'. The index can position on the range column, but
no column after it is used for the seek. Hence: order equality
columns before the range column.
— intro'd in lesson 0002
- filesort (Using filesort)
- An
EXPLAIN Extra flag meaning MySQL sorted the rows itself
rather than reading them pre-sorted from an index. Avoided when the ORDER BY
matches a leftmost prefix of a usable index in the same direction. Not necessarily on
disk — the name is historical.
— intro'd in lesson 0002
- selectivity / cardinality
- Cardinality is the number of distinct values in a column;
selectivity is that as a fraction of rows. High-selectivity columns
(many distinct values) filter to fewer rows per lookup, so they generally belong
earlier in a composite index.
— intro'd in lesson 0002
- Index Condition Pushdown (ICP)
- An optimization where MySQL evaluates the parts of a
WHERE it can against
the index itself before fetching the full row, cutting clustered-index lookups. Shows as
Using index condition in EXPLAIN.
— intro'd in lesson 0002
- EXPLAIN
- A statement that prints the optimizer's chosen execution plan for a query
without running it. Read five columns in order —
type, key, rows, filtered,
Extra — to grade the plan.
— intro'd in lesson 0003
- access type (the
type column)
- How MySQL reaches rows, on a best-to-worst ladder:
const →
eq_ref → ref → range → index →
ALL. The single most important value in a plan.
— intro'd in lesson 0003
- eq_ref vs ref
- eq_ref: exactly one row per join combination via a PK or
UNIQUE NOT NULL index — the best join type. ref: possibly
several rows, via a non-unique key or a leftmost prefix. Both are index lookups; eq_ref
guarantees uniqueness.
— intro'd in lesson 0003
- ALL (full table scan)
- The worst access type: every row of the table is examined. Fine on tiny tables, a red
flag on large ones — usually fixed by adding an index whose leftmost prefix matches the
filter.
— intro'd in lesson 0003
- rows & filtered
rows = estimated rows examined (an estimate for InnoDB);
filtered = estimated % surviving the WHERE. Their product is
the rows carried into the next join step — the query's funnel.
— intro'd in lesson 0003- EXPLAIN ANALYZE
- Runs the query and prints actual time and row counts beside the estimates, as
a tree of iterators (
FORMAT=TREE). A large estimated-vs-actual gap explains
most bad plans; refresh stats with ANALYZE TABLE.
— intro'd in lesson 0003
← lesson 0001 · All lessons