Optimizer Trace Guide
Optimizer trace uses the JSON format. It is basically a structured log file showing what actions were taken by the query optimizer.
A Basic Example
Let's take a simple query:
MariaDB> explain select * from t1 where a<10; +------+-------------+-------+-------+---------------+------+---------+------+------+-----------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +------+-------------+-------+-------+---------------+------+---------+------+------+-----------------------+ | 1 | SIMPLE | t1 | range | a | a | 5 | NULL | 10 | Using index condition | +------+-------------+-------+-------+---------------+------+---------+------+------+-----------------------+
One can see the full trace here. Taking only the component names, one gets:
MariaDB> select * from information_schema.optimizer_trace limit 1\G *************************** 1. row *************************** QUERY: select * from t1 where a<10 TRACE: { "steps": [ { "join_preparation": { ... } } }, { "join_optimization": { "select_id": 1, "steps": [ { "condition_processing": { ... } }, { "table_dependencies": [ ... ] }, { "ref_optimizer_key_uses": [ ... ] }, { "rows_estimation": [ { "range_analysis": { "analyzing_range_alternatives" : { ... }, "chosen_range_access_summary": { ... }, }, "selectivity_for_indexes" : { ... }, "selectivity_for_columns" : { ... } } ] }, { "considered_execution_plans": [ ... ] }, { "attaching_conditions_to_tables": { ... } } ] } }, { "join_execution": { ... } } ] }
Trace Structure
For each SELECT, there are two "Steps":
join_preparation
join_optimization
Join preparation shows early query rewrites. join_optmization
is where most of the query optimizations are done. They are:
condition_processing
- basic rewrites in WHERE/ON conditions.ref_optimizer_key_uses
- Construction of possible ways to do ref and eq_ref accesses.rows_estimation
- Consideration of range and index_merge accesses.considered_execution_plans
- Join optimization itself, that is, choice of the join order.attaching_conditions_to_tables
- Once the join order is fixed, parts of the WHERE clause are "attached" to tables to filter out rows as early as possible.
The above steps are for just one SELECT. If the query has subqueries, each SELECT will have these steps, and there will be extra steps/rewrites to handle the subquery construct itself.
Extracting trace components
If you are interested in some particular part of the trace, MariaDB has two functions that come in handy:
- JSON_EXTRACT extracts a part of JSON document
- JSON_DETAILED presents it in a user-readable way.
For example, the data about range optimization can be extracted like so: MariaDB> select JSON_DETAILED(JSON_EXTRACT(trace, '$.analyzing_range_alternatives')) -> from INFORMATION_SCHEMA.OPTIMIZER_TRACE\G ***** 1. row ***** JSON_DETAILED(JSON_EXTRACT(trace, '$.analyzing_range_alternatives')): [ { "range_scan_alternatives": [ { "index": "a_b_c", "ranges": [ "(1) <= (a,b) < (4,50)" ], "rowid_ordered": false, "using_mrr": false, "index_only": false, "rows": 4, "cost": 6.2509, "chosen": true } ], "analyzing_roworder_intersect": { "cause": "too few roworder scans" }, "analyzing_index_merge_union": [] } ]