How to fix poor performance of INFORMATION_SCHEMA.key_column_usage in MySQL?

You can use GLOBAL variable as shown below −

SET global innodb_stats_on_metadata =0;

After including the above syntax, the INFORMATION_SCHEMA.key_column_usage will take less time and that would improve the performance.

The query is as follows −

mysql> set global innodb_stats_on_metadata =0;
Query OK, 0 rows affected (0.00 sec)
mysql> SELECT REFERENCED_TABLE_NAME,TABLE_NAME,COLUMN_NAME,CONSTRAINT_SCHEMA
   -> FROM INFORMATION_SCHEMA.key_column_usage;

The following is the output −

It returns 674 rows in 0.28 seconds.