Engine Atlas

MySQL Internal Temporary Tables - RAM, mmap Overflow, and InnoDB Spill

GROUP BY · TempTable · filesort · LIMIT
MySQL 8.4.10captured evidence
한국어
Learning guide

Question for this Lab

While producing the same GROUP BY result, where do intermediate rows live in RAM, mmap-overflow, and InnoDB-on-disk profiles?

Execution engine: MySQL 8.4.10
Who runs it?

One session executes the same aggregation SELECT under three controlled profiles.

What stays controlled?

The work_orders fixture, GROUP BY, ORDER BY, LIMIT 100, and result checksum stay fixed.

Executed SQL
SELECT
  account_id,
  status,
  bucket_id,
  COUNT(*) AS order_count,
  SUM(amount_cents) AS total_cents,
  MAX(payload) AS sample_payload
FROM work_orders
GROUP BY account_id, status, bucket_id
ORDER BY total_cents DESC, account_id, status, bucket_id
LIMIT 100;
What changes?

The internal temporary-table allocation profile, memory counters, and modeled storage transition change.

What stays fixed?

Aggregate result rows, ordering semantics, and final checksum remain identical across all profiles.

Predict before tracing

What should happen to the query result when the storage profile changes?

What should happen to the query result when the storage profile changes?

Evidence mission

Open the storage-profile step of the InnoDB on-disk profile and identify which captured values produce that classification.

Where do you look?
Read the physical_ram high-water, physical_disk high-water, Created_tmp_disk_tables, and temptable_use_mmap setting together in the storage evidence panel.
Screen to open
InnoDB on-disk · storage profile
What should you see?
Expected observation

In this profile Created_tmp_disk_tables is 1, mmap is disabled, and physical_disk high-water does not rise above its baseline. The classification rests on those captured profile settings and counters, while the RAM-to-disk transition picture stays a Model.

Evidence boundary
Captured
profile settings with physical_ram and physical_disk high-water and Created_tmp_disk_tables
Model
storage conversion boundary without an exact row or time
Checkpoint · explain from evidenceCan Created_tmp_disk_tables alone establish the full mmap or InnoDB storage transition?Reveal answer and basis

No. Captured counters and profile settings are observed evidence. The displayed RAM, mmap, and InnoDB transition remains explicitly bounded as a Model.

Evidence boundary
Captured
profile settings and statement-counter observations
Model
RAM, mmap, and InnoDB storage transition
Core takeaway

Separate final rows from intermediate allocation, and keep captured counters distinct from the explanatory storage model.

Next connection

The next Lab traces the smaller storage units: a secondary-index entry, carried primary key, clustered row, and Buffer Pool page state.

Review terms
internal temporary tableIntroduced in Lab 05
Intermediate storage created and managed by the server inside a statement for work such as GROUP BY, DISTINCT, or ORDER BY.
filesortIntroduced in Lab 01
MySQL's name for an explicit sort used when index order cannot satisfy ORDER BY; it does not necessarily mean a disk file.

01 · SOURCE

Exact SQL

1 active fragmentMySQLGROUP BY · ORDER BY · LIMIT 100
SELECT account_id, status, bucket_id, COUNT(*) AS order_count, SUM(amount_cents) AS total_cents, MAX(payload) AS sample_payload
FROM work_orders
GROUP BY account_id, status, bucket_id
ORDER BY total_cents DESC, account_id, status, bucket_id
LIMIT 100;
  1. source-scan
  2. temporary-work
  3. temporary-scan
  4. filesort
  5. limit
02 · FLOW

Native plan and actual iterators

01 / 12
Active native operators5
limitlimit
TREE: -> Limit: 100 row(s) ANALYZE: -> Limit: 100 row(s) (actual time=222..222 rows=100 loops=1)TREE line 1; ANALYZE line 1
filesortsort
TREE: -> Sort: total_cents DESC, work_orders.account_id, work_orders.`status`, work_orders.bucket_id, limit input to 100 row(s) per chunk ANALYZE: -> Sort: total_cents DESC, work_orders.account_id, work_orders.`status`, work_orders.bucket_id, limit input to 100 row(s) per chunk (actual time=222..222 rows=100 loops=1)TREE line 2; ANALYZE line 2
temporary-scantable-scan
TREE: -> Table scan on <temporary> ANALYZE: -> Table scan on <temporary> (actual time=196..209 rows=65536 loops=1)TREE line 3; ANALYZE line 3
temporary-worktemp-table
TREE: -> Aggregate using temporary table ANALYZE: -> Aggregate using temporary table (actual time=196..196 rows=65536 loops=1)TREE line 4; ANALYZE line 4
source-scantable-scan
TREE: -> Table scan on work_orders (cost=15156 rows=128478) ANALYZE: -> Table scan on work_orders (cost=13513 rows=128478) (actual time=0.037..40.3 rows=131072 loops=1)TREE line 5; ANALYZE line 5
Exact execution detail

131072 source rows become 100 result rows

How does this SQL reduce 131072 source rows to 100 result rows?

GROUP BY output

Logical temporary rows

Derived · fixture
65536 rows65536 omitted from this bounded view
Active metrics4
source scan actual rows
131072 row
Captured
limit actual rows
100 row
Captured
execution rows examined
131172 row
Captured
execution rows sent
100 row
Captured
01 / 12