Deep into MySQL QEP with optimizer_trace

 

Deep into MySQL QEP with optimizer_trace

Louis Hust

 

0  Preface

MySQL5.6 add a new option to show QEP more deeply-optimizer_trace. This option is very different from EXPLAIN which just showes the table access method, index using, table join order and so on. But optimizer_trace output every steps of how the Optimizer processes query, such as join prepare, join optimizer and join exec. Each of the three steps contains many substeps such as expanded_query, equality_propagation, trivial_condition_removal, etc.

 

0  Show QEP in deep

# Turn tracing on (it's off by default):
SET optimizer_trace="enabled=on";
SELECT * FROM t1 WHERE c1=1 or c1=100;# your query here
SELECT * FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE;
# possibly more queries...
# When done with tracing, disable it:
SET optimizer_trace="enabled=off";

 

An example below:

 

{
  "steps": [
    {
      "join_preparation": {
        "select#": 1,
        "steps": [
          {
            "expanded_query": "/* select#1 */ select `t1`.`c1` AS `c1`,`t1`.`c2` AS `c2` from `t1` where ((`t1`.`c1` = 1) or (`t1`.`c1` = 100))"
          }
        ]
      }
    },
    {
      "join_optimization": {
        "select#": 1,
        "steps": [
          {
            "condition_processing": {
              "condition": "WHERE",
              "original_condition": "((`t1`.`c1` = 1) or (`t1`.`c1` = 100))",
              "steps": [
                {
                  "transformation": "equality_propagation",
                  "resulting_condition": "(multiple equal(1, `t1`.`c1`) or multiple equal(100, `t1`.`c1`))"
                },
                {
                  "transformation": "constant_propagation",
                  "resulting_condition": "(multiple equal(1, `t1`.`c1`) or multiple equal(100, `t1`.`c1`))"
                },
                {
                  "transformation": "trivial_condition_removal",
                  "resulting_condition": "(multiple equal(1, `t1`.`c1`) or multiple equal(100, `t1`.`c1`))"
                }
              ]
            }
          },
          {
            "table_dependencies": [
              {
                "table": "`t1`",
                "row_may_be_null": false,
                "map_bit": 0,
                "depends_on_map_bits": [
                ]
              }
            ]
          },
          {
            "ref_optimizer_key_uses": [
            ]
          },
          {
            "rows_estimation": [
              {
                "table": "`t1`",
                "range_analysis": {
                  "table_scan": {
                    "rows": 10157,
                    "cost": 2057.5
                  },
                  "potential_range_indices": [
                    {
                      "index": "c1",
                      "usable": true,
                      "key_parts": [
                        "c1"
                      ]
                    }
                  ],
                  "setup_range_conditions": [
                  ],
                  "group_index_range": {
                    "chosen": false,
                    "cause": "not_group_by_or_distinct"
                  },
                  "analyzing_range_alternatives": {
                    "range_scan_alternatives": [
                      {
                        "index": "c1",
                        "ranges": [
                          "1 <= c1 <= 1",
                          "100 <= c1 <= 100"
                        ],
                        "index_dives_for_eq_ranges": true,
                        "rowid_ordered": false,
                        "using_mrr": false,
                        "index_only": false,
                        "rows": 2,
                        "cost": 4.41,
                        "chosen": true
                      }
                    ],
                    "analyzing_roworder_intersect": {
                      "usable": false,
                      "cause": "too_few_roworder_scans"
                    }
                  },
                  "chosen_range_access_summary": {
                    "range_access_plan": {
                      "type": "range_scan",
                      "index": "c1",
                      "rows": 2,
                      "ranges": [
                        "1 <= c1 <= 1",
                        "100 <= c1 <= 100"
                      ]
                    },
                    "rows_for_plan": 2,
                    "cost_for_plan": 4.41,
                    "chosen": true
                  }
                }
              }
            ]
          },
          {
            "considered_execution_plans": [
              {
                "plan_prefix": [
                ],
                "table": "`t1`",
                "best_access_path": {
                  "considered_access_paths": [
                    {
                      "access_type": "range",
                      "rows": 2,
                      "cost": 4.81,
                      "chosen": true
                    }
                  ]
                },
                "cost_for_plan": 4.81,
                "rows_for_plan": 2,
                "chosen": true
              }
            ]
          },
          {
            "attaching_conditions_to_tables": {
              "original_condition": "((`t1`.`c1` = 1) or (`t1`.`c1` = 100))",
              "attached_conditions_computation": [
              ],
              "attached_conditions_summary": [
                {
                  "table": "`t1`",
                  "attached": "((`t1`.`c1` = 1) or (`t1`.`c1` = 100))"
                }
              ]
            }
          },
          {
            "refine_plan": [
              {
                "table": "`t1`",
                "pushed_index_condition": "((`t1`.`c1` = 1) or (`t1`.`c1` = 100))",
                "table_condition_attached": null,
                "access_type": "range"
              }
            ]
          }
        ]
      }
    },
    {
      "join_explain": {
        "select#": 1,
        "steps": [
        ]
      }
    }
  ]
}

 

As seen above, we can get almost every step in Optimizer processing, from setup conditions to plan choosing. The result is output according to order of the code executing, but EXPLAIN is just a print of JOIN_TAB. I can not explain every step, cause I do not know every steps.

 

Actually the Optimizer code of MySQL is hard to read, with the trace, we can read the code more easily.

 

References

[1]

MySQL Internals

---

File translated from T_EX by T_TH, version 4.03.
On 5 Jan 2013, 19:30.