Optimizer applies LIMIT-scaled range cost against unscaled table scan cost, choosing a range plan that cannot terminate early

Disclaimer: The following writeup was created with LLM assistance after an extensive debugging session. The issue is confirmed in production with our data and with a minimal test case. Optimizer traces of the original query (LIMIT 5000) and one that triggers the faster table scan plan (LIMIT 10000) are attached, as well as a minimal reproducer.

Summary

For a single-table query with ORDER BY ... LIMIT, the join optimizer correctly costs a full table scan as cheaper than range access and selects it. A later optimization phase (attaching_conditions_to_tables) re-runs range analysis, applies a LIMIT-based cost reduction to the range alternative only, and compares that reduced cost against the unscaled table scan cost from the earlier phase. The range plan wins this asymmetric comparison and replaces the chosen plan.

The LIMIT reduction is unjustified in this case because the chosen index cannot satisfy the ORDER BY. A filesort is required, so every qualifying row must be read before the first row of the result can be produced, and no early termination is possible. The optimizer's own test_if_skip_sort_order step is empty, confirming the sort cannot be skipped.

The result is a plan that reads ~433k index entries plus the corresponding clustered index lookups instead of a sequential scan of the same table, at roughly 3.5x the execution time.

Demonstration

Requirements:

• Single table, no joins.
• A secondary index whose leading column is matched by an equality predicate that is highly non-selective (here namespace = 0, matching 97.4% of rows).
• ORDER BY on columns not covered by that index, so a filesort is mandatory.
• LIMIT n where n is below the optimizer's estimated post-filter row count. This is the trigger; see "Boundary condition" below.

Table: 447,115 rows. Index namespace is (namespace, objectname). Query orders by (date, pagename).

SELECT extradata, pagename, date, icon, icondark, shortname, tournament,

       series, opponentname, opponenttemplate, opponentplayers, opponenttype

FROM wikifortnite_lpdb_placement

WHERE date != '0000-01-01 00:00:00.000000'

  AND month(date) = '8' AND day(date) = '12' AND year(date) < '2026'

  AND placement = '1' AND opponentname != 'TBD' AND prizepoolindex = '1'

  AND (liquipediatier = '1' OR liquipediatier = '2')

  AND liquipediatiertype != 'Qualifier'

  AND namespace = 0

ORDER BY date ASC, pagename ASC

LIMIT 5000;

Optimizer behaviour

Phase 1 — join optimization (correct)

"considered_access_paths": [

  { "access_type": "ref", "index": "namespace", "rows": 435673, "cost": 467.0307886, "chosen": true },

  { "access_type": "scan", "rows": 447115, "rows_after_filter": 5277.856548,

    "rows_out": 5277.856548, "cost": 100.4972206, "chosen": true }

],

"chosen_access_method": { "type": "scan", "cost": 100.4972206 }

cost_for_plan: 100.4972206, best_join_order: ["lpdb_placement"].

Phase 2 — attaching_conditions_to_tables (incorrect)

Range analysis is re-run. The namespace alternative now carries an additional field:

{

  "index": "namespace",

  "ranges": ["(0) <= (namespace) <= (0)"],

  "rowid_ordered": true,

  "rows": 435673,

  "cost": 467.0315798,

  "cost_with_limit": 16.82209224,

  "chosen": true

}

"chosen_range_access_summary": {

  "range_access_plan": { "type": "range_scan", "index": "namespace", "rows": 435673 },

  "rows_for_plan": 435673,

  "cost_for_plan": 467.0315798,

  "chosen": true

}

467.03 is reduced to 16.82 and beats the table scan's 100.50. The table scan is never re-costed under the same LIMIT assumption. No cost_for_sorting is computed in this phase.

test_if_skip_sort_order is empty and the plan performs a filesort, so the early-termination behaviour that cost_with_limit models does not occur:

"table_name": "lpdb_placement",

"access_type": "range",

"key": "namespace",

"r_index_rows": 433400,

"r_rows": 433400,

"r_engine_stats": { "pages_accessed": 1301797 },

"r_filtered": 6.922012e-4

Why the trigger condition is met: row estimate

cond_selectivity is 0.011804248, giving rows_after_filter of 5277.86 against an actual result of 3 rows. Histograms only cover the sargable predicates:

"placement":          0.039712378

"liquipediatier":     0.368108875

"liquipediatiertype": 0.919796920

"prizepoolindex":     0.968730640

"date":               0.954461380

The month(date) = 8 AND dayofmonth(date) = 12 predicates are non-sargable and contribute no selectivity, so the estimate is high by roughly three orders of magnitude. A more accurate estimate would place rows_out well below the LIMIT.

Boundary condition

The defect fires only when the LIMIT is binding, i.e. when the estimated post-filter row count exceeds it. With LIMIT 10000 against the same rows_after_filter of 5277.9 the limit is non-binding, the second phase does no range analysis at all — attached_conditions_computation is empty — and the correct table scan plan survives. The phase-1 trace for that run also computes cost_for_sorting: 3.488276893 and marks the scan path use_tmp_table: true, i.e. the sort is priced as part of the plan.

This makes the bug latent and data-dependent: identical query text against tables with different distributions will intermittently produce either plan, depending on which side of the LIMIT the row estimate falls.