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: