CODEWITHSUNDEEP
mysqlsqlperformanceinformation-schema
Mathieu
Mathieu

Reputation: 1677

Poor performance of INFORMATION_SCHEMA.key_column_usage in MySQL

I'm running version 5.5.11 of MySQL and the performance when querying the INFORMATION_SCHEMA.key_column_usage table is really bad.

I have a simple select request: 

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

It takes, 8 seconds in average to return 400 rows. Is this a know issue? If so, is there a way to improve performance (a patch maybe?).

Upvotes: 7

Views: 4073

Answers (2)

nfroidure
nfroidure

Reputation: 1601

By using the tip given there : http://www.mysqlperformanceblog.com/2011/12/23/solving-information_schema-slowness/

I switched from seconds to a hundred millisecond for the same query. This setting, saved my day :

innodb_stats_on_metadata=0

Upvotes: 10

Mathieu
Mathieu

Reputation: 1677

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 got massive performance improvements, coming from 8 seconds to 0.2!

Upvotes: 4

Related Questions