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
6
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
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
6
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
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!