Optimizer Tracing


A a new tracing capability has been added to the MySQL optimizer with 5.6.3. Optimizer Tracing goes beyond EXPLAIN EXTENDED to show how the optimizer treats queries. The demonstration of this feature at Oracle Open World had several noted DBAs speechless.

SET optimizer_trace="enabled=on";
SELECT Name FROM City WHERE ID=999;
SELECT trace into dumpfile '/tmp/foo' FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE;

And the trace follows (sadly WordPress kills the indentations, PDF of trace here)


{
"steps": [
{
"join_preparation": {
"select#": 1,
"steps": [
{
"expanded_query": "/* select#1 */ select `world`.`City`.`Name` AS `Name` from `world`.`City` where (`world`.`City`.`ID` = 999)"
}
]
}
},
{
"join_optimization": {
"select#": 1,
"steps": [
{
"condition_processing": {
"condition": "WHERE",
"original_condition": "(`world`.`City`.`ID` = 999)",
"steps": [
{
"transformation": "equality_propagation",
"resulting_condition": "multiple equal(999, `world`.`City`.`ID`)"
},
{
"transformation": "constant_propagation",
"resulting_condition": "multiple equal(999, `world`.`City`.`ID`)"
},
{
"transformation": "trivial_condition_removal",
"resulting_condition": "multiple equal(999, `world`.`City`.`ID`)"
}
]
}
},
{
"table_dependencies": [
{
"database": "world",
"table": "City",
"row_may_be_null": false,
"map_bit": 0,
"depends_on_map_bits": [
]
}
]
},
{
"ref_optimizer_key_uses": [
{
"database": "world",
"table": "City",
"field": "ID",
"equals": "999",
"null_rejecting": false
}
]
},
{
"rows_estimation": [
{
"database": "world",
"table": "City",
"rows": 1,
"cost": 1,
"table_type": "const"
}
]
},
{
"attaching_conditions_to_tables": {
"original_condition": "1",
"attached_conditions_computation": [
],
"attached_conditions_summary": [
]
}
},
{
"refine_plan": [
]
}
]
}
},
{
"join_execution": {
"select#": 1,
"steps": [
]
}
}
]
}

Be sure to read:

MySQL Optimizer tracing: Query Execution Plan descriptions beyond EXPLAIN
http://jorgenloland.blogspot.com/2011/10/optimizer-tracing-query-execution-plan.html

MySQL Optimizer tracing: how to configure it
http://guilhembichot.blogspot.com/2011/09/optimizer-tracing-how-to-configure-it.html

MySQL Optimizer: new EXPLAIN for data-modifiers
http://glebshchepa.blogspot.com/2011/10/optimizer-new-explain-for-data.html

About these ads

Leave a comment

Filed under labs.mysql.com, MySQL

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s