MarkLeith/mysql-sys — pull request #24 by JesperWisborgKrogh (2014-09-01 04:36:14+00:00)
Title: schema_table_statistics and schema_table_statistics_with_buffer performance

By replacing the LEFT JOIN on performance_schema.file_summary_by_instance with a view using the TEMPTABLE algorithm selecting the required data, the optimizer can add an ad-hoc index (auto key) which can greatly improve performance.

Together with the change to the extract_schema_from_file_name() and extract_table_from_file_name() functions so they return a varchar(64), the improvement on my laptop for 100 schemas with 100 tables each is from 14+ minutes to <30 seconds for schema_table_statistics.