Q & A on Webinar “Introduction to MySQL Query Tuning for DevOps”

First I want to thank everyone who attended my December 5, 2019 webinar “Introduction to MySQL Query Tuning for DevOps“. Recording and slides are available on the webinar page.

Here are answers to the questions from participants which I was not able to provide during the webinar.

Q: How to find stored execution plans and optimizer metadata stored in mysql data dictionary (i.e. PS, IS, sys schema)?

A: The Optimizer creates the query execution plan each time when MySQL Server executes the query. These plans are never stored.

However, some information, used by the optimizer, to create the execution plan, is stored and available. It includes.

1. Index statistics. You can find details using the SHOW INDEX  command:
   
   MySQL

   mysql> show index from employees; +-----------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | +-----------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ | employees | 0 | PRIMARY | 1 | emp_no | A | 299556 | NULL | NULL | | BTREE | | | | employees | 1 | first_name | 1 | first_name | A | 1196 | NULL | NULL | | BTREE | | | | employees | 1 | first_name | 2 | last_name | A | 280646 | NULL | NULL | | BTREE | | | +-----------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ 3 rows in set (0,00 sec)

   1 2 3 4 5 6 7 8 9

   mysql> show index from employees; +-----------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ | Table     | Non_unique | Key_name   | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | +-----------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ | employees |          0 | PRIMARY    |            1 | emp_no      | A         |      299556 |     NULL | NULL   |      | BTREE      |         |               | | employees |          1 | first_name |            1 | first_name  | A         |        1196 |     NULL | NULL   |      | BTREE      |         |               | | employees |          1 | first_name |            2 | last_name   | A         |      280646 |     NULL | NULL   |      | BTREE      |         |               | +-----------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ 3 rows in set (0,00 sec)

   
   Or by querying information_schema.statistics  table:
   
   Shell

   mysql> select * from information_schema.statistics where TABLE_SCHEMA='employees' and table_name='employees'; +---------------+--------------+------------+------------+--------------+------------+--------------+-------------+-----------+-------------+----------+--------+----------+------------+---------+---------------+ | TABLE_CATALOG | TABLE_SCHEMA | TABLE_NAME | NON_UNIQUE | INDEX_SCHEMA | INDEX_NAME | SEQ_IN_INDEX | COLUMN_NAME | COLLATION | CARDINALITY | SUB_PART | PACKED | NULLABLE | INDEX_TYPE | COMMENT | INDEX_COMMENT | +---------------+--------------+------------+------------+--------------+------------+--------------+-------------+-----------+-------------+----------+--------+----------+------------+---------+---------------+ | def | employees | employees | 0 | employees | PRIMARY | 1 | emp_no | A | 299556 | NULL | NULL | | BTREE | | | | def | employees | employees | 1 | employees | first_name | 1 | first_name | A | 1196 | NULL | NULL | | BTREE | | | | def | employees | employees | 1 | employees | first_name | 2 | last_name | A | 280646 | NULL | NULL | | BTREE | | | +---------------+--------------+------------+------------+--------------+------------+--------------+-------------+-----------+-------------+----------+--------+----------+------------+---------+---------------+ 3 rows in set (0,00 sec)

   1 2 3 4 5 6 7 8 9

   mysql> select * from information_schema.statistics where TABLE_SCHEMA='employees' and table_name='employees'; +---------------+--------------+------------+------------+--------------+------------+--------------+-------------+-----------+-------------+----------+--------+----------+------------+---------+---------------+ | TABLE_CATALOG | TABLE_SCHEMA | TABLE_NAME | NON_UNIQUE | INDEX_SCHEMA | INDEX_NAME | SEQ_IN_INDEX | COLUMN_NAME | COLLATION | CARDINALITY | SUB_PART | PACKED | NULLABLE | INDEX_TYPE | COMMENT | INDEX_COMMENT | +---------------+--------------+------------+------------+--------------+------------+--------------+-------------+-----------+-------------+----------+--------+----------+------------+---------+---------------+ | def           | employees    | employees  |          0 | employees    | PRIMARY    |            1 | emp_no      | A         |      299556 |     NULL | NULL   |          | BTREE      |         |               | | def           | employees    | employees  |          1 | employees    | first_name |            1 | first_name  | A         |        1196 |     NULL | NULL   |          | BTREE      |         |               | | def           | employees    | employees  |          1 | employees    | first_name |            2 | last_name   | A         |      280646 |     NULL | NULL   |          | BTREE      |         |               | +---------------+--------------+------------+------------+--------------+------------+--------------+-------------+-----------+-------------+----------+--------+----------+------------+---------+---------------+ 3 rows in set (0,00 sec)

2. For InnoDB tables, you can additionally query mysql.innodb_index_stats  table which stores physical data which the engine passes to the Optimizer:
   
   Shell

   mysql> select * from mysql.innodb_index_stats where database_name='employees' and table_name='employees'; +---------------+------------+------------+---------------------+--------------+------------+-------------+-----------------------------------+ | database_name | table_name | index_name | last_update | stat_name | stat_value | sample_size | stat_description | +---------------+------------+------------+---------------------+--------------+------------+-------------+-----------------------------------+ | employees | employees | PRIMARY | 2019-12-12 18:22:40 | n_diff_pfx01 | 299556 | 20 | emp_no | | employees | employees | PRIMARY | 2019-12-12 18:22:40 | n_leaf_pages | 886 | NULL | Number of leaf pages in the index | | employees | employees | PRIMARY | 2019-12-12 18:22:40 | size | 929 | NULL | Number of pages in the index | | employees | employees | first_name | 2019-12-12 21:49:02 | n_diff_pfx01 | 1196 | 20 | first_name | | employees | employees | first_name | 2019-12-12 21:49:02 | n_diff_pfx02 | 280646 | 20 | first_name,last_name | | employees | employees | first_name | 2019-12-12 21:49:02 | n_diff_pfx03 | 298471 | 20 | first_name,last_name,emp_no | | employees | employees | first_name | 2019-12-12 21:49:02 | n_leaf_pages | 460 | NULL | Number of leaf pages in the index | | employees | employees | first_name | 2019-12-12 21:49:02 | size | 545 | NULL | Number of pages in the index | +---------------+------------+------------+---------------------+--------------+------------+-------------+-----------------------------------+ 8 rows in set (0,01 sec)

   1 2 3 4 5 6 7 8 9 10 11 12 13 14

   mysql> select * from mysql.innodb_index_stats where database_name='employees' and table_name='employees'; +---------------+------------+------------+---------------------+--------------+------------+-------------+-----------------------------------+ | database_name | table_name | index_name | last_update         | stat_name    | stat_value | sample_size | stat_description                  | +---------------+------------+------------+---------------------+--------------+------------+-------------+-----------------------------------+ | employees     | employees  | PRIMARY    | 2019-12-12 18:22:40 | n_diff_pfx01 |     299556 |          20 | emp_no                            | | employees     | employees  | PRIMARY    | 2019-12-12 18:22:40 | n_leaf_pages |        886 |        NULL | Number of leaf pages in the index | | employees     | employees  | PRIMARY    | 2019-12-12 18:22:40 | size         |        929 |        NULL | Number of pages in the index      | | employees     | employees  | first_name | 2019-12-12 21:49:02 | n_diff_pfx01 |       1196 |          20 | first_name                        | | employees     | employees  | first_name | 2019-12-12 21:49:02 | n_diff_pfx02 |     280646 |          20 | first_name,last_name              | | employees     | employees  | first_name | 2019-12-12 21:49:02 | n_diff_pfx03 |     298471 |          20 | first_name,last_name,emp_no       | | employees     | employees  | first_name | 2019-12-12 21:49:02 | n_leaf_pages |        460 |        NULL | Number of leaf pages in the index | | employees     | employees  | first_name | 2019-12-12 21:49:02 | size         |        545 |        NULL | Number of pages in the index      | +---------------+------------+------------+---------------------+--------------+------------+-------------+-----------------------------------+ 8 rows in set (0,01 sec)

   
   Difference between SHOW INDEX  output and mysql.innodb_index_stats  table content is that information in SHOW INDEX  is absolutely virtual and calculated on each access while data in the mysql.innodb_index_stats  table physically stored and updated only when InnoDB storage engine updates statistics.
3. Since version 8.0: Optimizer statistics aka Histograms. You can find details by querying information_schema.column_statistics  table:
   
   Shell

   mysql> select HISTOGRAM from information_schema.column_statistics -> where table_name=’example’\G *************************** 1. row *************************** HISTOGRAM: {"buckets": [[1, 0.6], [2, 0.8], [3, 1.0]], "data-type": "int", "null-values": 0.0, "collation-id": 8, "last-updated": "2018-11-07 09:07:19.791470", "sampling-rate": 1.0, "histogram-type": "singleton", "number-of-buckets-specified": 3} 1 row in set (0.00 sec)

   1 2 3 4 5 6 7 8 9

   mysql> select HISTOGRAM from information_schema.column_statistics     -> where table_name=’example’\G *************************** 1. row *************************** HISTOGRAM: {"buckets": [[1, 0.6], [2, 0.8], [3, 1.0]], "data-type": "int", "null-values": 0.0, "collation-id": 8, "last-updated": "2018-11-07 09:07:19.791470", "sampling-rate": 1.0, "histogram-type": "singleton", "number-of-buckets-specified": 3} 1 row in set (0.00 sec)

4. Optimizer trace: which actions Optimizer performed to resolve last optimizer_trace_limit queries. This data stored in memory, disabled by default and available in the information_schema.optimizer_trace  table:
   
   Shell

   mysql> set optimizer_trace=1; Query OK, 0 rows affected (0,00 sec) mysql> select count(*) from employees where first_name like 'A%'; +----------+ | count(*) | +----------+ | 22039 | +----------+ 1 row in set (0,16 sec) mysql> select * from information_schema.optimizer_trace\G *************************** 1. row *************************** QUERY: select count(*) from employees where first_name like 'A%' TRACE: { "steps": [ { "join_preparation": { "select#": 1, "steps": [ { "expanded_query": "/* select#1 */ select count(0) AS `count(*)` from `employees` where (`employees`.`first_name` like 'A%')" } ] } }, { "join_optimization": { "select#": 1, "steps": [ { ...

   1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32

   mysql> set optimizer_trace=1; Query OK, 0 rows affected (0,00 sec)   mysql> select count(*) from employees where first_name like 'A%'; +----------+ | count(*) | +----------+ |    22039 | +----------+ 1 row in set (0,16 sec)   mysql> select * from information_schema.optimizer_trace\G *************************** 1. row ***************************                             QUERY: select count(*) from employees where first_name like 'A%'                             TRACE: {   "steps": [     {       "join_preparation": {         "select#": 1,         "steps": [           {             "expanded_query": "/* select#1 */ select count(0) AS `count(*)` from `employees` where (`employees`.`first_name` like 'A%')"           }         ]       }     },     {       "join_optimization": {         "select#": 1,         "steps": [           { ...

Q: Is it possible to list all possible plans available to optimizer for an individual query? how?

A: Since query execution plans created each time when the query is executed it is not possible to list them all.

Q: How to clear an existing plan for a particular query or all optimizer metadata from the data dictionary?

A: Since query execution plans are not stored anywhere, there is no need to clear them.

Q: Hey there, I have been working with mysql for a long time, now I want to make a system that will have complex queries with a combination of group by columns, I want them to get completed in a couple of seconds and use the lowest ram, your advise may be helpful.

A: This is a complicated question. I recommend you to learn how MySQL can use indexes for GROUP BY . Start from “GROUP BY Optimization” chapter of the MySQL User Reference Manual.