MySQL中的INFORMATION_SCHEMA.key_column_usage性能不佳

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

我正在运行MySQL的5.5.11版本,查询INFORMATION_SCHEMA.key_column_usage表时的性能非常糟糕。

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?).

平均需要8秒才能返回400行。这是一个知道问题吗?如果是这样,有没有办法提高性能(补丁可能?)。

2 个解决方案

#1

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

通过使用给出的提示: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

#2

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

我在这里找到了一篇有趣的文章: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!

我在我的查询中添加了WHERE TABLE_SCHEMA ='myTable',我获得了大量的性能提升,从8秒到0.2!

#1