Tracking MySQL Query Plans Over Time Part One

 Did you ever need to determine the performance of a query over time? One of the problems with database query optimizations is that the underlying data is constantly churning. Add in an increase in the number of users, expanding server demand use, and other items that impact your query. What was optimized is no longer performing as expected.

But how do you tell if there is a change in the query plan or the query performance? Well, the first step is gathering the needed information.

MySQL added a JSON format option to the output of the EXPLAIN command quite a while ago. More recently, Oracle added the feature of saving the output into a variable (see https://dev.mysql.com/doc/refman/9.0/en/mysql-nutshell.html and https://dev.mysql.com/blog-archive/explain-into-and-explain-for-schema-in-mysql-81-and-82). This provides a way to access the data in that variable and plop it into a database. The saved result can be compared to current or other past responses for analysis. 

mysql> explain format=json into @var
       SELECT * from city where District='Texas';
Query OK, 0 rows affected (0.00 sec)
mysql> select json_pretty(@var)\G
*************************** 1. row ***************************
json_pretty(@var): {
  "query_block": {
    "table": {
      "filtered": "10.00",
      "cost_info": {
        "eval_cost": "40.46",
        "read_cost": "370.39",
        "prefix_cost": "410.85",
        "data_read_per_join": "97K"
      },
      "table_name": "city",
      "access_type": "ALL",
      "used_columns": [
        "ID",
        "Name",
        "CountryCode",
        "District",
        "Population"
      ],
      "attached_condition": "(`world`.`city`.`District` = 'Texas')",
      "rows_examined_per_scan": 4046,
      "rows_produced_per_join": 404
    },
    "cost_info": {
      "query_cost": "410.85"
    },
    "select_id": 1
  }
}
1 row in set (0.00 sec)
MySQL> 

The detailed items are easy to access.

mysql> SELECT JSON_EXTRACT(@var, "$.query_block.cost_info.query_cost") AS cost;
+----------+
| cost     |
+----------+
| "410.85" |
+----------+
1 row in set (0.00 sec)

In Part Two, let's gather the various items we want to track to study query performance over time.