Engine Atlas

Database FoundationsMySQL 8.4.10

Learn database concepts and real execution as one path

Start with tables, rows, and keys, or move directly into real MySQL execution to trace indexes, joins, versions, locks, and waits.

View the Foundations path I know the basics — open LAB 01

New to databases

Start with Database Foundations

Before inspecting one engine, learn what schemas, current rows, keys, and constraints mean through the same customers and orders data.

FOUNDATION 01 · Available now
  1. 01

    Separate a schema from its current rows.

  2. 02

    Manipulate how primary and foreign keys decide a write.

  3. 03

    See the six-lesson path into SQL results and transactions.

Start Foundation 01 View all Foundations

Learning path

Learn eight Labs as one three-stage path

Start with access and filtering, then follow physical work, row versions, locks, and waits. Each Lab shows the prerequisite and learning question before you open it.

STAGE 01

Read a query path

Build the core path from SQL clauses to access, filtering, joining, sorting, and result rows.

  1. LAB 01

    Order Search Execution

    What row path does the same SQL create with a full scan versus a composite index?

    Prerequisite Basic SELECT · JOIN · WHERE · ORDER BY · LIMIT
    What you will trace

    Compare the table, index, filter, join, sort, and result flow produced by the same order query with a full scan and a composite index.

    Open lab
  2. LAB 02

    Composite Index Anatomy

    How does index column order change the equality prefix, first range, ICP, and base-row lookup?

    Prerequisite LAB 01 · access path, filter, sort
    What you will trace

    Switch among three index orders and follow the equality prefix, first range, ICP, base-row lookup, and index-only projection through real plans and rows.

    Open lab
  3. LAB 03

    Cardinality & Histograms

    Why can a changed estimate alter optimizer decisions when the result rows stay the same?

    Prerequisite LAB 01 · rows, filtered, filesort
    What you will trace

    Compare how absent, fresh, and stale histograms change filtered and estimated Filter rows over identical data, grounded in captured buckets, plans, and 128 actual rows.

    Open lab

STAGE 02

Follow operators and physical work

Connect join algorithms, internal temporary work, and secondary-to-clustered lookup to exact rows and pages.

  1. LAB 04

    Nested Loop vs Hash Join

    In what order do Hash Join build/probe and Nested Loop outer/inner lookup encounter rows?

    Prerequisite LAB 01 · join row flow
    What you will trace

    Compare Hash Join build/probe flow with Indexed Nested Loop outer/inner lookups over identical SQL and fixture data, grounded in native plans, actual rows and loops, and exact matched pairs.

    Open lab
  2. LAB 05

    Internal Temporary Work

    How do GROUP BY rows pass through temporary work, filesort, and memory or disk profiles before LIMIT returns 100 rows?

    Prerequisite LAB 03 · GROUP BY, sort, LIMIT
    What you will trace

    Trace the internal temporary rows, filesort, and LIMIT boundary produced by one GROUP BY query across RAM, mmap overflow, and InnoDB on-disk profiles, grounded in exact rows and captured memory.

    Open lab
  3. LAB 06

    Secondary Index & Buffer Pool

    Which clustered row and Buffer Pool page does a primary key carried by a secondary entry reach?

    Prerequisite LAB 02 · secondary index, base-row lookup
    What you will trace

    Trace one non-covering range query from a secondary entry and carried primary key to its clustered row, then compare captured cold and warm Buffer Pool pages and counters.

    Open lab

STAGE 03

Follow versions and waits

Connect transaction snapshots, row versions, record/gap locks, waits, and COMMIT.

  1. LAB 07

    MVCC Consistent Read

    Which row version does the same SELECT read under each isolation level and COMMIT point?

    Prerequisite LAB 06 · clustered row · transaction basics
    What you will trace

    Compare which row versions the same secondary-index SELECT reads before and after the writer COMMIT under REPEATABLE READ and READ COMMITTED, grounded in transactions, an undo-traversal Model, and captured results.

    Open lab
  2. LAB 08

    Locking Read: FOR UPDATE and Next-Key Lock

    Which records and gaps does a FOR UPDATE range lock, who waits, and what resumes after COMMIT?

    Prerequisite LAB 07 · snapshot, COMMIT, row version
    What you will trace

    Compare whether Session A's SELECT makes Session B's INSERT and Session C's UPDATE wait. The same WHERE clause is replayed as a plain SELECT, REPEATABLE READ + FOR UPDATE, and READ COMMITTED + FOR UPDATE.

    Open lab
Order Search Execution Workbench showing SQL, Execution Plan, Data Lens, and Evidence together
Featured Lab Order Search Execution

Compare how the same SQL produces a full scan and a composite-index access path, grounded in actual rows.

Workbench

Four surfaces point to the same execution step

  1. SQL

    The active clause and predicate

  2. Execution Plan

    The active operator, access type, selected key, rows, and cost

  3. Data Lens

    Table scans, index candidates, filters, joins, sorting, and result rows

  4. Plan & Evidence

    The raw plan line and source behind each metric

Evidence methodology

Keep engine evidence separate from explanatory Models

Every visible value is labeled by where it comes from.

Captured
EXPLAIN, Performance Schema, Handler counters, and results from MySQL 8.4.10
Derived · fixture
Exact row sets calculated by applying the same predicate to the fixed fixture
Logical model
A B-tree teaching model built from the index definition and candidate keys