Cost‑Based MySQL Query Optimization: Concepts, Tools, and Practical Tips

Last week, Xu Jitao from the MySQL team presented a webinar on MySQL query optimization. The talk was organized into five main topics: cost‑based optimization, monitoring and analysis tools, data‑access methods and index selection, join optimization, and influencing the optimizer.

1. Cost‑Based MySQL Query Optimization – The optimizer receives a SQL statement and generates an execution plan by evaluating possible join orders, index usage, and other operations. It assigns a cost to each operation, primarily based on the estimated number of rows accessed, and selects the plan with the lowest total cost. The cost model uses statistics from the data dictionary and storage engine, such as row counts, index cardinality, and column metadata. Cost constants are stored in MySQL tables and can be tuned.

2. Monitoring, Analysis, and Query‑Optimization Tools – Various tools help observe MySQL performance, including the MySQL Enterprise Monitor query analyzer, Performance Schema tables (e.g., events_statements_history , events_statements_summary_by_digest , file_summary_by_event_name , table_io_waits_summary ), and the sys_schema view collection. These provide statistics such as execution time (TIMER_WAIT), rows examined vs. rows sent, and temporary disk tables created. EXPLAIN (table, JSON, visual) and MySQL Workbench can display execution plans.

3. Data‑Access Methods and Index Selection – For each table in a query, the optimizer evaluates possible access methods, estimates their costs, and chooses the cheapest one. Detailed information on access methods is available in the accompanying video.

4. Join Optimization – The join optimizer searches for the best join order using a greedy, depth‑first strategy, pruning plans that already exceed the best cost found. An example with tables Language, Country, and City shows how the optimizer evaluates different orders, ultimately selecting Country → Language → City as the lowest‑cost plan.

5. Influencing the Optimizer – MySQL 5.7 introduced optimizer hints placed in special comments after SELECT, allowing fine‑grained control of features via optimizer_switch variables. MySQL 8.0 adds hints for derived table and view merging, as well as more flexible join‑order hints. Session variables can be set temporarily during a query, and a plugin can rewrite problematic queries on the fly, e.g., adding FORCE INDEX hints based on performance‑schema summaries.

The webinar video is linked at the end of the article for a more detailed walkthrough.