Slow performance INFORMATION_SCHEMA.key_column_usage in MySQL

Question

I am running MySQL version 5.5.11, and the performance when querying the INFORMATION_SCHEMA.key_column_usage table is very poor.

I have a simple query to choose:

SELECT REFERENCED_TABLE_NAME , TABLE_NAME AS TableName , COLUMN_NAME AS ColumnName , CONSTRAINT_SCHEMA AS Db FROM INFORMATION_SCHEMA.key_column_usage 

It takes an average of 8 seconds to return 400 rows. Is this a problem? If so, is there a way to improve performance (perhaps a patch?).

Answer 1

Using the tip given here: http://www.mysqlperformanceblog.com/2011/12/23/solving-information_schema-slowness/

I went from seconds to one hundred milliseconds for the same request. This option saved my day:

 innodb_stats_on_metadata=0 

Answer 2

I found an interesting article here: http://dev.mysql.com/doc/refman/5.5/en/information-schema-optimization.html

I added WHERE TABLE_SCHEMA = 'myTable' to my query, and I had big performance improvements starting from 8 seconds to 0.2!