Posted inMySQL

Mastering OPTIMIZER_TRACE: The Pro Technique for Dissecting MySQL Index Selection Logic

MySQL tutorial - IT technology blog
MySQL tutorial - IT technology blog

When EXPLAIN is No Longer Enough

I once managed an e-commerce system where the orders table exceeded 20 million rows. One day, a query filtering orders became unusually slow, taking 15 seconds to respond. Even though the status and created_at columns were indexed, MySQL decided to perform a Full Table Scan.

The standard EXPLAIN command only tells you what MySQL is doing. It confirms a full table scan is happening but doesn’t explain why it bypassed the index. Why does the optimizer evaluate the cost of Index X as higher than Index Y? That’s when you need a more specialized solution to “dissect” the root cause.

OPTIMIZER_TRACE is the MRI scanner for SQL. It reveals the entire “thought process” of the MySQL Optimizer. You will clearly see every cost calculation behind each index selection decision.

Comparing Query Inspection Tools

Each tool has its own purpose. Understanding them will save you hours of fruitless debugging.

1. EXPLAIN (Basic)

  • Pros: Quickly shows the general execution plan.
  • Cons: Too vague. It cannot explain why an index was excluded (pruned).

2. EXPLAIN ANALYZE (MySQL 8.0+)

  • Pros: Measures the actual execution time of each step. Excellent for finding bottlenecks.
  • Cons: You must wait for the query to finish to get results. If the query hangs the server, this tool is quite dangerous.

3. OPTIMIZER_TRACE (In-depth)

  • Pros: Explains logic based on costs (Cost-based). It shows the exact expected I/O and CPU figures for each option.
  • Cons: The output is a very long JSON. You need patience to read and understand it.

Pros and Cons in Practice

Through many DB optimization projects, I’ve gathered some important notes about this tool.

Key Benefits:

  • Cost Transparency: MySQL calculates 1.0 cost unit for every disk page read. The trace will show you the total cost of a Table Scan versus an Index Scan.
  • Detecting Index Merge Issues: Sometimes MySQL merges two indexes, but it ends up slower than using a single index. The trace will highlight this mistake.
  • Range Optimizer Analysis: Particularly effective for IN(...) statements containing thousands of values.

Limitations to Note:

  • High Overhead: Writing the trace consumes CPU and RAM. Never enable it globally on Production. It should only be used for the current session.
  • Memory Limits: If the trace is too long, the result may be truncated. You need to increase the optimizer_trace_max_mem_size variable to about 1MB or more.

4-Step Implementation Process

Don’t just run it blindly. Follow the process below to get the most accurate data.

Step 1: Enable Trace for the Session

This command only affects your current connection and does not impact other users.

SET SESSION optimizer_trace="enabled=on";
-- Increase buffer to avoid losing trace data
SET SESSION optimizer_trace_max_mem_size=1048576;

Step 2: Execute the Query to be Inspected

Run the SQL statement you want to optimize. If the table is too large, you can add a LIMIT. The Optimizer will still calculate the same logic before applying the limit.

SELECT * FROM orders WHERE customer_id = 5001 AND status = 'COMPLETED';

Step 3: Extract the JSON Data

The data is located in the virtual system table INFORMATION_SCHEMA.

SELECT TRACE FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE;

Step 4: Clean Up Resources

Always turn off the trace immediately after finishing to return resources to the server.

SET SESSION optimizer_trace="enabled=off";

Understanding the JSON: Where to Focus?

The trace result can be thousands of lines long. Don’t read everything. Focus on the join_optimization section, which contains the most important calculations.

The considered_paths Section

This is where MySQL lists the Index “candidates.” Look at the following example:

"chosen_range_access_summary": {
  "range_access_plan": {
    "type": "range_scan",
    "index": "idx_status",
    "rows": 150240,
    "cost": 18201,
    "chosen": false,
    "cause": "cost"
  }
}

If chosen is false, MySQL will tell you why. Usually, it’s because the cost is higher than a full table scan or another index.

Real-world Experience: When the Optimizer is “Fooled”

Once, I encountered a case where MySQL predicted 10,000 rows in the trace, but there were actually only 10 rows. This discrepancy caused it to choose a severely incorrect Index.

Solution: When the rows_estimation in the trace doesn’t match reality, immediately run the command ANALYZE TABLE table_name;. This command updates data distribution statistics, helping the Optimizer regain its “vision.”

Additionally, be careful with Index Merge. If the trace shows MySQL is trying to merge multiple individual indexes, consider creating a Composite Index (multi-column index). In my experience, a Composite Index always provides more stable and predictable performance.

Conclusion

Mastering OPTIMIZER_TRACE helps you move away from guesswork when optimizing a database. This is an indispensable tool if you want to handle complex queries on large data systems. Combine it with EXPLAIN ANALYZE for the most comprehensive view of your SQL performance.

Share:
Tags: