Compare MySQL 8.4.10 estimated rows with 128 actual Filter rows across absent, fresh, and stale histograms over the same 131,072-row data set.
No Histogram
- access_type
ALL- selected key
null- scan estimate
- 130917
- filtered
- 10.00%
- produced estimate
- 13091
- Filter actual
- 128
- absolute error
- 12963
- filesort input
- 128
- Sort / LIMIT output
- 100
Histogram
COLUMN_STATISTICS: 0 rows
Read the predicate, ordering, and LIMIT together
The query searches 131,072 order_events rows for sales_channel='PARTNER', sorts by ordered_at and id descending, and returns the newest 100 rows.
All three plans use the same ALL access
There is no sales_channel secondary index, so the selected key is null and access type is ALL, followed by Filter, filesort, and Limit.
COLUMN_STATISTICS has no sales_channel row
The metadata query returned zero rows, so histogramPresent is false; this is an observed query result rather than a missing-file fallback.
The final data distribution is identical in every variant
The final counts are DIRECT 65,408, INTERNAL 32,768, MARKETPLACE 32,768, and PARTNER 128, giving PARTNER an actual share of 0.09765625%.
There is no histogram bucket for PARTNER
No singleton bucket or cumulative frequency is present, while the exact final count of 128 remains available from the fixture distribution.
filtered 10.00% estimates 13,091 rows
Applying filtered 10.00% to the 130,917-row scan estimate gives 13,091.7, while native rows_produced_per_join is 13,091.
The table scan reads 131,072 actual rows once
The EXPLAIN ANALYZE Table scan iterator reports 131,072 actual rows and one loop, separately from the optimizer's scan estimate.
sales_channel='PARTNER' leaves 128 rows
The fixed fixture passes 128 rows and rejects 130,944; the Filter iterator independently reports 128 actual rows and one loop.
The 13,091-row estimate misses actual 128 by 12,963
The comparison uses the estimated Filter output, not the Table scan estimate, and this plan substantially overestimates the actual Filter rows.
Filesort receives 128 rows and returns 100
The 128 Filter rows feed Top-N Sort. Sort and LIMIT output 100 rows, SORT_ROWS is 100, the cutoff is id 116, and id 112 is next excluded.
All three variants return the same newest 100 rows
The result IDs descend by four from 512 through 116 with checksum 16e6f9b5…9ecd; histogram state does not change result values or order.
Reconcile histogram absence with the 10.00% estimate
Compare the zero-row COLUMN_STATISTICS result with EXPLAIN ALL, filtered, rows_produced, EXPLAIN ANALYZE actual rows, counters, and the 100-row result.
Captured 100-row result
MySQL 8.4.10 ·
16e6f9b59437241a7ed4ccff509bb6afb870a02f79d0dd45718f52812bec9ecd
Fresh Histogram
- access_type
ALL- selected key
null- scan estimate
- 130917
- filtered
- 0.10%
- produced estimate
- 130
- Filter actual
- 128
- absolute error
- 2
- filesort input
- 128
- Sort / LIMIT output
- 100
Histogram
singleton · sampling 1.0 · manual · 4 buckets
Read the predicate, ordering, and LIMIT together
The query searches 131,072 order_events rows for sales_channel='PARTNER', sorts by ordered_at and id descending, and returns the newest 100 rows.
All three plans use the same ALL access
There is no sales_channel secondary index, so the selected key is null and access type is ALL, followed by Filter, filesort, and Limit.
The Fresh Histogram was built after the skew mutation
This singleton histogram was manually updated from the final distribution, with sampling-rate 1.0, four buckets, and auto-update false.
The final data distribution is identical in every variant
The final counts are DIRECT 65,408, INTERNAL 32,768, MARKETPLACE 32,768, and PARTNER 128, giving PARTNER an actual share of 0.09765625%.
CDF subtraction gives PARTNER mass 0.0009765625
The difference from MARKETPLACE CDF 0.9990234375 to PARTNER CDF 1.0 is the PARTNER value mass, equal to 128/131,072.
filtered 0.10% estimates 130 rows
The displayed 0.10% over a 130,917-row scan estimate gives 130.917, while native rows_produced_per_join is the integer 130.
The table scan reads 131,072 actual rows once
The EXPLAIN ANALYZE Table scan iterator reports 131,072 actual rows and one loop, separately from the optimizer's scan estimate.
sales_channel='PARTNER' leaves 128 rows
The fixed fixture passes 128 rows and rejects 130,944; the Filter iterator independently reports 128 actual rows and one loop.
The 130-row estimate misses actual 128 by two
The PARTNER share remembered by the Fresh Histogram matches the final distribution, keeping the Filter output estimate close to 128 actual rows.
Filesort receives 128 rows and returns 100
The 128 Filter rows feed Top-N Sort. Sort and LIMIT output 100 rows, SORT_ROWS is 100, the cutoff is id 116, and id 112 is next excluded.
All three variants return the same newest 100 rows
The result IDs descend by four from 512 through 116 with checksum 16e6f9b5…9ecd; histogram state does not change result values or order.
Connect the fresh buckets, 130-row estimate, and actual 128
Reconcile raw histogram CDFs with EXPLAIN filtered and rows_produced, Filter actual rows, SORT_ROWS 100, and the same result checksum.
Captured 100-row result
MySQL 8.4.10 ·
16e6f9b59437241a7ed4ccff509bb6afb870a02f79d0dd45718f52812bec9ecd
Stale Histogram
- access_type
ALL- selected key
null- scan estimate
- 130917
- filtered
- 25.00%
- produced estimate
- 32729
- Filter actual
- 128
- absolute error
- 32601
- filesort input
- 128
- Sort / LIMIT output
- 100
Histogram
singleton · sampling 1.0 · manual · 4 buckets
Read the predicate, ordering, and LIMIT together
The query searches 131,072 order_events rows for sales_channel='PARTNER', sorts by ordered_at and id descending, and returns the newest 100 rows.
All three plans use the same ALL access
There is no sales_channel secondary index, so the selected key is null and access type is ALL, followed by Filter, filesort, and Limit.
The pre-mutation Histogram remains after ordinary ANALYZE
Raw JSON, buckets, and timestamp from the manually created uniform-distribution histogram remain identical after mutation and ordinary ANALYZE TABLE.
The final data distribution is identical in every variant
The final counts are DIRECT 65,408, INTERNAL 32,768, MARKETPLACE 32,768, and PARTNER 128, giving PARTNER an actual share of 0.09765625%.
The Stale Histogram remembers PARTNER mass as 0.25
It retains the initial CDF where each of four values held 25%, far from the current PARTNER share of 0.0009765625.
filtered 25.00% estimates 32,729 rows
Twenty-five percent of the 130,917-row scan estimate is 32,729.25; native rows_produced_per_join is 32,729, close to the initial 32,768 PARTNER rows.
The table scan reads 131,072 actual rows once
The EXPLAIN ANALYZE Table scan iterator reports 131,072 actual rows and one loop, separately from the optimizer's scan estimate.
sales_channel='PARTNER' leaves 128 rows
The fixed fixture passes 128 rows and rejects 130,944; the Filter iterator independently reports 128 actual rows and one loop.
The 32,729-row estimate misses actual 128 by 32,601
The stale 25% mass greatly overestimates current Filter output; the data and result stay identical while statistics state and estimate differ.
Filesort receives 128 rows and returns 100
The 128 Filter rows feed Top-N Sort. Sort and LIMIT output 100 rows, SORT_ROWS is 100, the cutoff is id 116, and id 112 is next excluded.
All three variants return the same newest 100 rows
The result IDs descend by four from 512 through 116 with checksum 16e6f9b5…9ecd; histogram state does not change result values or order.
Reconcile the unchanged stale CDF with the 32,729-row estimate
Compare identical pre/post-mutation raw histograms with EXPLAIN's 25.00% estimate, 128 actual Filter rows, and the same sort and result.
Captured 100-row result
MySQL 8.4.10 ·
16e6f9b59437241a7ed4ccff509bb6afb870a02f79d0dd45718f52812bec9ecd