Mysql – Why are these queries slow in some environments and not others? Can we fix their performance

information-schemaMySQLperformancestored-procedures

The following stored procedures in a MySQL 5.x environment:

DELIMITER //

CREATE PROCEDURE sp_belongs_to (IN schemaName VARCHAR(100), IN tableName VARCHAR(100))
    BEGIN
        SELECT TABLE_NAME as keyTable, GROUP_CONCAT(COLUMN_NAME) AS keyColumns, REFERENCED_TABLE_NAME AS refTable, GROUP_CONCAT(REFERENCED_COLUMN_NAME) AS refColumns, CONSTRAINT_NAME AS constraintName
        FROM INFORMATION_SCHEMA.key_column_usage
        WHERE TABLE_SCHEMA = schemaName  COLLATE utf8_general_ci
        AND TABLE_NAME = tableName  COLLATE utf8_general_ci
        AND REFERENCED_TABLE_NAME IS NOT NULL
        GROUP BY constraintName;
    END //

CREATE PROCEDURE sp_has_many (IN schemaName VARCHAR(100), IN tableName VARCHAR(100))
    BEGIN
      SELECT REFERENCED_TABLE_NAME AS keyTable, GROUP_CONCAT(REFERENCED_COLUMN_NAME) AS keyColumns, TABLE_NAME AS refTable, GROUP_CONCAT(COLUMN_NAME) AS refColumns, CONSTRAINT_NAME AS constraintName
      FROM INFORMATION_SCHEMA.key_column_usage
      WHERE TABLE_SCHEMA = schemaName COLLATE utf8_general_ci
      AND REFERENCED_TABLE_NAME = tableName COLLATE utf8_general_ci
      GROUP BY constraintName;
    END //

DELIMITER ;

In one environment they run in a .03 seconds and in another closer to 1 or even 2 seconds.

Our current best guess is that it is primarily a problem with the number of tables and amount of data in one environment versus another.

Can anyone provide additional insights? And is there a way to accomplish the same thing as these procedures while getting more consistent performance?

Best Answer

You may want to disable the variable innodb_stats_on_metadata.

According to the MySQL Documentation on innodb_stats_on_metadata

When this variable is enabled (which is the default, as before the variable was created), InnoDB updates statistics during metadata statements such as SHOW TABLE STATUS or SHOW INDEX, or when accessing the INFORMATION_SCHEMA tables TABLES or STATISTICS. (These updates are similar to what happens for ANALYZE TABLE.) When disabled, InnoDB does not update statistics during these operations. Disabling this variable can improve access speed for schemas that have a large number of tables or indexes. It can also improve the stability of execution plans for queries that involve InnoDB tables.

Related Question