設定
mysql> show variables like 'opttimizer_trace';
Empty set (0.00 sec)
mysql> show variables like 'optimizer_trace';
+-----------------+-----------------------------------------+
| Variable_name | Value |
+-----------------+-----------------------------------------+
| optimizer_trace | enabled=off,end_marker=off,one_line=off |
+-----------------+-----------------------------------------+
1 row in set (0.00 sec)
mysql> set optimizer_trace='enabled=on';
Query OK, 0 rows affected (0.00 sec)
mysql> show variables like 'optimizer_trace';
+-----------------+----------------------------------------+
| Variable_name | Value |
+-----------------+----------------------------------------+
| optimizer_trace | enabled=on,end_marker=off,one_line=off |
+-----------------+----------------------------------------+
1 row in set (0.00 sec)
SQL実行、information_schema.optimizer_trace参照
mysql> select * from hoge;
Empty set (0.00 sec)
mysql> select * from information_schema.OPTIMIZER_TRACE;
+--------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-----------------------------------+-------------------------+
| QUERY | TRACE | MISSING_BYTES_BEYOND_MAX_MEM_SIZE | INSUFFICIENT_PRIVILEGES |
+--------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-----------------------------------+-------------------------+
| select * from hoge | {
"steps": [
{
"join_preparation": {
"select#": 1,
"steps": [
{
"expanded_query": "/* select#1 */ select `hogedb`.`hoge`.`id` AS `id` from `hogedb`.`hoge`"
}
]
}
},
{
"join_optimization": {
"select#": 1,
"steps": [
{
"table_dependencies": [
{
"database": "hogedb",
"table": "hoge",
"row_may_be_null": false,
"map_bit": 0,
"depends_on_map_bits": [
]
}
]
},
{
"rows_estimation": [
{
"database": "hogedb",
"table": "hoge",
"table_scan": {
"rows": 1,
"cost": 1
}
}
]
},
{
"considered_execution_plans": [
{
"database": "hogedb",
"table": "hoge",
"best_access_path": {
"considered_access_paths": [
{
"access_type": "scan",
"rows": 1,
"cost": 1,
"chosen": true
}
]
},
"cost_for_plan": 1.2,
"rows_for_plan": 1,
"chosen": true
}
]
},
{
"attaching_conditions_to_tables": {
"original_condition": null,
"attached_conditions_computation": [
],
"attached_conditions_summary": [
{
"database": "hogedb",
"table": "hoge",
"attached": null
}
]
}
},
{
"refine_plan": [
{
"database": "hogedb",
"table": "hoge",
"access_type": "table_scan"
}
]
}
]
}
},
{
"join_execution": {
"select#": 1,
"steps": [
]
}
}
]
} | 0 | 0 |
+--------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-----------------------------------+-------------------------+
1 row in set (0.00 sec)
推奨ではないけどこういう設定方法も出来る
mysql> show variables like 'optimizer_trace';
+-----------------+-----------------------------------------+
| Variable_name | Value |
+-----------------+-----------------------------------------+
| optimizer_trace | enabled=off,end_marker=off,one_line=off |
+-----------------+-----------------------------------------+
1 row in set (0.00 sec)
mysql> set optimizer_trace=1;
Query OK, 0 rows affected (0.00 sec)
mysql> show variables like 'optimizer_trace';
+-----------------+----------------------------------------+
| Variable_name | Value |
+-----------------+----------------------------------------+
| optimizer_trace | enabled=on,end_marker=off,one_line=off |
+-----------------+----------------------------------------+
1 row in set (0.00 sec)