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 always 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.
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.
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>
Blog Reboot
Planet MySQL