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

QA FollowupFirst 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:

    Or by querying information_schema.statistics  table:
  2. For InnoDB tables, you can additionally query mysql.innodb_index_stats  table which stores physical data which the engine passes to the Optimizer:

    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:
  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:

 

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.

Share this post

Leave a Reply