Intermediate LevelSQL & MySQL
How does a Cost-Based Optimizer (CBO) choose query execution plans?
❓ Interview Prompt: Explain Cost-Based Optimizer (CBO), calculating disk I/O and CPU cost metrics, table statistics (`ANALYZE TABLE`), and index selectivity.
The Cost-Based Optimizer (CBO) calculates the estimated computational cost (Disk I/O and CPU cycles) of various candidate execution paths before selecting the cheapest execution plan:
Inputs Evaluated:
1
Table Statistics: Row counts, average row length, and data page counts.
2
Index Selectivity: Cardinality (number of unique values in an index).
•
⚠️ Stale Statistics Pitfall: Outdated table statistics cause the CBO to choose bad execution plans! Fix by running 'ANALYZE TABLE table_name'.
Code Implementation Example
sql
-- Refreshing stale table statistics for the Cost-Based Optimizer
ANALYZE TABLE orders, order_items;💡Updating InnoDB table statistics via ANALYZE TABLE.
🎯 Interview Key Takeaway
Run ANALYZE TABLE periodically to refresh table statistics and help the Cost-Based Optimizer choose accurate execution plans.
#SQL#Optimizer#EXPLAIN#CBO#Intermediate
Back to all SQL & MySQL Questions→