AbstractMySQLPlatform::getListTableForeignKeysSQL is substantially slower following 3.3 update

[rtek]

Bug Report

Q	A
BC Break	no
Version	3.3.0

Summary

AbstractMySQLPlatform::getListTableForeignKeysSQL is significantly slower than 3.2 because it introduces an inefficient join between INFORMATION_SCHEMA.KEY_COLUMN_USAGE and INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS in mysql 5.7.

This problem does not manifest in smaller databases since size of the two tables is relatively small.

Current behavior

Here is the 3.3 SQL

       return 'SELECT k.CONSTRAINT_NAME, k.COLUMN_NAME, k.REFERENCED_TABLE_NAME, ' .
               'k.REFERENCED_COLUMN_NAME /*!50116 , c.UPDATE_RULE, c.DELETE_RULE */ ' .
               'FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE k /*!50116 ' .
               'INNER JOIN INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS c ON ' .
               'c.CONSTRAINT_NAME = k.CONSTRAINT_NAME AND ' .
               'c.TABLE_NAME = k.TABLE_NAME AND ' .
               'c.CONSTRAINT_SCHEMA = k.CONSTRAINT_SCHEMA */ ' .
               'WHERE k.TABLE_NAME = ' . $this->quoteStringLiteral($table) . ' ' .
               'AND k.TABLE_SCHEMA = ' . $this->getDatabaseNameSQL($database) . ' ' .
               'ORDER BY k.ORDINAL_POSITION';

With a sample query

select k.CONSTRAINT_NAME, k.COLUMN_NAME, k.REFERENCED_TABLE_NAME, k.REFERENCED_COLUMN_NAME /*!50116 , c.UPDATE_RULE, c.DELETE_RULE */
from INFORMATION_SCHEMA.KEY_COLUMN_USAGE k /*!50116 INNER JOIN INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS c ON c.CONSTRAINT_NAME = k.CONSTRAINT_NAME AND c.TABLE_NAME = k.TABLE_NAME AND c.CONSTRAINT_SCHEMA = k.CONSTRAINT_SCHEMA */
where k.TABLE_NAME = 'foo' and k.TABLE_SCHEMA = 'bar' order by k.ORDINAL_POSITION

Execution time: ~800ms

Explain

    id  select_type  table   partitions  type    possible_keys  key                      key_len  ref       rows  filtered  Extra                                                                                       
------  -----------  ------  ----------  ------  -------------  -----------------------  -------  ------  ------  --------  --------------------------------------------------------------------------------------------
     1  SIMPLE       k       (NULL)      ALL     (NULL)         TABLE_SCHEMA,TABLE_NAME  (NULL)   (NULL)  (NULL)    (NULL)  Using where; Open_full_table; Scanned 0 databases; Using temporary; Using filesort          
     1  SIMPLE       c       (NULL)      ALL     (NULL)         (NULL)                   (NULL)   (NULL)  (NULL)    (NULL)  Using where; Open_full_table; Scanned all databases; Using join buffer (Block Nested Loop)  

Expected behavior

Moving the table name back into the join (as was in 3.2) fixes the issue

       return 'SELECT k.CONSTRAINT_NAME, k.COLUMN_NAME, k.REFERENCED_TABLE_NAME, ' .
               'k.REFERENCED_COLUMN_NAME /*!50116 , c.UPDATE_RULE, c.DELETE_RULE */ ' .
               'FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE k /*!50116 ' .
               'INNER JOIN INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS c ON ' .
               'c.CONSTRAINT_NAME = k.CONSTRAINT_NAME AND ' .
               'c.TABLE_NAME = ' . $this->quoteStringLiteral($table) . ' AND ' .
               'c.CONSTRAINT_SCHEMA = k.CONSTRAINT_SCHEMA */ ' .
               'WHERE k.TABLE_NAME = ' . $this->quoteStringLiteral($table) . ' ' .
               'AND k.TABLE_SCHEMA = ' . $this->getDatabaseNameSQL($database) . ' ' .
               'ORDER BY k.ORDINAL_POSITION';

select k.CONSTRAINT_NAME, k.COLUMN_NAME, k.REFERENCED_TABLE_NAME, k.REFERENCED_COLUMN_NAME /*!50116 , c.UPDATE_RULE, c.DELETE_RULE */
from INFORMATION_SCHEMA.KEY_COLUMN_USAGE k /*!50116 INNER JOIN INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS c ON c.CONSTRAINT_NAME = k.CONSTRAINT_NAME AND c.TABLE_NAME = 'foo' AND c.CONSTRAINT_SCHEMA = k.CONSTRAINT_SCHEMA */
where k.TABLE_NAME = 'foo' and k.TABLE_SCHEMA = 'bar' order by k.ORDINAL_POSITION

Execution time: ~5ms

Explain

    id  select_type  table   partitions  type    possible_keys  key                      key_len  ref       rows  filtered  Extra                                                                                    
------  -----------  ------  ----------  ------  -------------  -----------------------  -------  ------  ------  --------  -----------------------------------------------------------------------------------------
     1  SIMPLE       k       (NULL)      ALL     (NULL)         TABLE_SCHEMA,TABLE_NAME  (NULL)   (NULL)  (NULL)    (NULL)  Using where; Open_full_table; Scanned 0 databases; Using temporary; Using filesort       
     1  SIMPLE       c       (NULL)      ALL     (NULL)         TABLE_NAME               (NULL)   (NULL)  (NULL)    (NULL)  Using where; Open_full_table; Scanned 1 database; Using join buffer (Block Nested Loop)

While the two queries are logically equivalent, the optimizer is hinting at something different via scanned all database vs scanned 1 database.

[rtek]

duplicate of #5195

[github-actions]

This thread has been automatically locked since there has not been any recent activity after it was closed. Please open a new issue for related bugs.