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
- sargable
- "Search-ARGument-able" — a predicate an index can seek on: the bare indexed
column compared directly to a type-compatible constant (
col = ?,
col >= ?, col LIKE 'x%'). Wrapping the column in a function,
mismatching its type, or a leading wildcard makes it non-sargable → full scan.
— intro'd in lesson 0004
- functional index (functional key part)
- An index on an expression's value rather than a raw column, e.g.
INDEX ((UPPER(last_name))) — MySQL 8.0.13+. Lets a filter on that same
expression seek the index. Implemented as a hidden virtual generated column.
— intro'd in lesson 0004
- leading wildcard
- A
LIKE pattern that starts with % or _
('%pat', '%pat%'). Matches are scattered through the sorted
index, so no seek is possible — full scan. A right-anchored 'pat%' is a
usable prefix (range).
— intro'd in lesson 0004
- implicit type conversion
- When a comparison forces MySQL to coerce types — classically an indexed string column
vs a number (
str_col = 1). Because many strings map to one number, the index
can't be used. Match types (str_col = '1') and align join column
types/collations to keep the index.
— intro'd in lesson 0004
- selectivity (revisited)
- Low-selectivity filters (few distinct values, e.g. a boolean-ish
status)
match a large fraction of rows, so a secondary index would cause a bookmark lookup per
row — costlier than one full scan. The optimizer then picks ALL on purpose;
that's correct, not a bug.
— intro'd in lesson 0004
- transaction
- A unit of work that is atomic (all-or-nothing) and durable once committed:
START TRANSACTION … COMMIT (or ROLLBACK). Isolation and
consistency govern how concurrent transactions see each other.
— intro'd in lesson 0005
- isolation level
- The dial choosing which concurrency anomalies a transaction tolerates: READ UNCOMMITTED
→ READ COMMITTED → REPEATABLE READ (InnoDB default) → SERIALIZABLE. Set with
SET [SESSION|GLOBAL] TRANSACTION ISOLATION LEVEL ….
— intro'd in lesson 0005
- MVCC (multi-version concurrency control)
- InnoDB keeps multiple versions of rows so a query can read a consistent
snapshot without locking. This is why readers don't block writers and writers
don't block readers.
— intro'd in lesson 0005
- consistent (nonlocking) read
- A plain
SELECT served from an MVCC snapshot, setting no locks.
The default read mode under READ COMMITTED and REPEATABLE READ. Contrast a
locking read (SELECT … FOR UPDATE / FOR SHARE).
— intro'd in lesson 0005
- snapshot
- The point-in-time view of committed data an MVCC read sees. Under REPEATABLE READ it is
established at the transaction's first read and reused; under READ COMMITTED a fresh one is
taken per statement.
— intro'd in lesson 0005
- dirty / non-repeatable / phantom read
- The three anomalies. Dirty: reading uncommitted data.
Non-repeatable: a row's value changes between two reads (committed UPDATE).
Phantom: new rows match a repeated
WHERE (committed INSERT).
Higher isolation levels rule out more of them.
— intro'd in lesson 0005
- REPEATABLE READ vs READ COMMITTED
- Both use MVCC snapshots; the only difference is timing. RR: one snapshot
at the first read (reads repeat identically). RC: a fresh snapshot per
statement (reads may change mid-transaction).
— intro'd in lesson 0005
- record lock
- A lock on a single index record. InnoDB always locks index records (even
the clustered index when no secondary index exists). Prevents others updating/deleting that
row.
— intro'd in lesson 0006
- gap lock
- A lock on the gap between index records (or before the first / after the
last). "Purely inhibitive" — its only job is to block inserts into the gap. The
mechanism that prevents phantoms.
— intro'd in lesson 0006
- next-key lock
- A record lock + the gap lock before that record. InnoDB's default for
searches and index scans under REPEATABLE READ, which is how locking reads avoid phantom
rows.
— intro'd in lesson 0006
- shared (S) vs exclusive (X) lock
- S: lets the holder read the row; multiple S locks coexist
(
SELECT … FOR SHARE). X: lets the holder update/delete and
blocks all other locks (UPDATE, DELETE,
SELECT … FOR UPDATE).
— intro'd in lesson 0006
- locking read
- A
SELECT … FOR UPDATE (X) or SELECT … FOR SHARE (S) that takes
locks, unlike a plain snapshot SELECT. Use when you'll write based on what you
read.
— intro'd in lesson 0006
- deadlock (+ victim)
- A cycle where each transaction holds a lock another needs, so none can proceed. InnoDB
detects it and rolls back one — the victim (error 1213,
ER_LOCK_DEADLOCK). Applications should retry. Reduce with small txns, consistent
lock order, and indexed WHEREs.
— intro'd in lesson 0006
- write-ahead logging (WAL)
- The discipline of recording a change in the log before applying it to the data
files. InnoDB makes only the sequential redo append durable at
COMMIT; data
pages flush later. Log first, pages later.
— intro'd in lesson 0007
- redo log
- A disk-based, sequential log of changes used during crash recovery to
replay committed work that hadn't reached the data files. Rolls the database
forward. Its size counter is the LSN.
— intro'd in lesson 0007
- undo log
- Records how to reverse a transaction's changes to clustered-index records. Powers
ROLLBACK (roll back) and supplies earlier row versions for MVCC
consistent reads (0005). Double duty: rollback + snapshots.
— intro'd in lesson 0007
- innodb_flush_log_at_trx_commit
- The durability dial. 1 (default): write+fsync redo at each commit — full
ACID. 2: write to OS cache each commit, fsync ~1s — lose ≤1s only on
OS/power crash. 0: fsync ~1s regardless — lose ≤1s even on a mysqld crash.
— intro'd in lesson 0007
- buffer pool / dirty page
- The buffer pool is InnoDB's in-memory cache of pages. A dirty
page is one modified in memory but not yet flushed to the data files — safe because
its change is replayable from the redo log.
— intro'd in lesson 0007
- LSN / checkpoint
- The LSN (log sequence number) is the redo log's ever-growing byte offset;
every change advances it. A checkpoint marks how far dirty pages are
guaranteed flushed, letting older redo be reused.
— intro'd in lesson 0007
- binary log (binlog)
- A server-layer logical log of every committed change (row or statement),
used for replication and point-in-time recovery. Distinct
from the redo log (physical, InnoDB-internal, crash recovery). Format is
ROW
(default), STATEMENT, or MIXED.
— intro'd in lesson 0008
- relay log
- On a replica, the local copy of the source's binlog events written by the receiver (I/O)
thread, from which the applier (SQL) thread executes them against the replica's data.
— intro'd in lesson 0008
- receiver (I/O) vs applier (SQL) thread
- The two replica threads: the receiver pulls binlog events into the relay
log; the applier executes relay-log events against the replica's data. Their
gap is replication lag.
— intro'd in lesson 0008
- asynchronous vs semisynchronous replication
- Async (default): the source commits without waiting for replicas — fast,
but a failover can lose acknowledged commits. Semisync: the commit waits for
≥1 replica to receive (flush to relay log, not apply) the events, guaranteeing a
committed txn reached a replica, at one round-trip of latency.
— intro'd in lesson 0008
← lesson 0001 · All lessons