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

Published by

Stoker

Dave Stokes is a MySQL Community Manager for Oracle and previously was the MySQL Certification Manager for MySQL AB and Sun Microsystems. He has a MBA from the University Of San Diego.

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