TopolearnWhen the optimizer picks the wrong index, these are the tools that fix it without rewriting the application.
MySQL's cost-based optimizer estimates rows from statistics:
- Index statistics (cardinality) are sampled from a number of random pages, persisted (
innodb_stats_persistent = ON) and recalculated automatically after about 10% of rows change, or onANALYZE TABLE. RaiseSTATS_SAMPLE_PAGESper table when estimates for skewed data are poor. - Histograms (8.0) describe value distributions of non-indexed columns, so the optimizer can estimate selectivity of
WHERE status = 'refunded'and pick a better join order:ANALYZE TABLE orders UPDATE HISTOGRAM ON status WITH 32 BUCKETS. They aren't refreshed automatically byANALYZE TABLEalone; rebuild them as data changes. - For equality ranges, the optimizer does index dives (actually probing the index) up to
eq_range_index_dive_limitvalues in anINlist, then falls back to statistics.
When it still chooses badly:
- Optimizer hints in a comment after
SELECT:/*+ INDEX(o idx_customer) */,NO_INDEX,JOIN_ORDER(a, b),HASH_JOIN/NO_HASH_JOIN,MAX_EXECUTION_TIME(1000),SET_VAR(optimizer_switch = '...'). They apply to one statement and are ignored if invalid. - Older index hints:
FORCE INDEX (idx),USE INDEX,IGNORE INDEX. - The optimizer trace (
SET optimizer_trace = 'enabled=on', then readinformation_schema.optimizer_trace) shows every alternative considered and its cost.
Prefer fixing statistics over hints: hints freeze a plan that may become wrong as data changes.
Example
ANALYZE TABLE orders UPDATE HISTOGRAM ON status WITH 32 BUCKETS;
SELECT column_name, JSON_EXTRACT(histogram, '$."histogram-type"') AS type
FROM information_schema.column_statistics WHERE table_name = 'orders';
-- status | "singleton"
ALTER TABLE orders STATS_SAMPLE_PAGES = 200;
ANALYZE TABLE orders; -- refresh index statistics
SELECT /*+ INDEX(o idx_customer) MAX_EXECUTION_TIME(2000) */ o.id, o.total
FROM orders o
WHERE o.customer_id = 42 AND o.status = 'refunded';
SET optimizer_trace = 'enabled=on';
SELECT id FROM orders WHERE customer_id = 42 AND status = 'refunded';
SELECT JSON_EXTRACT(trace, '$**.considered_access_paths')
FROM information_schema.optimizer_trace;
SET optimizer_trace = 'enabled=off';Remember
- Index statistics are sampled; ANALYZE TABLE refreshes them
- Histograms give the optimizer distributions for non-indexed columns
- Use /+ hints / sparingly; the optimizer trace explains its choices
Watch out for
- Adding FORCE INDEX everywhere instead of fixing statistics, then suffering when data distribution changes.