Menu
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?).
532
16 The INFORMATION_SCHEMA KEY_COLUMN_USAGE Table. The KEY_COLUMN_USAGE table describes which key columns have constraints. This table provides no information about functional key parts because they are expressions and the table provides information only about columns.
INFORMATION_SCHEMA provides access to database metadata, information about the MySQL server such as the name of a database or table, the data type of a column, or access privileges. Other terms that are sometimes used for this information are data dictionary and system catalog.
The INFORMATION_SCHEMA database is an ANSI standard set of views we can find in SQL Server, but also MySQL. Other database systems also have either exactly such or similar database implemented. It provides the read-only access to details related to databases and their objects (tables, constraints, procedures, views…)
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
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!
45
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
