You signed in with another tab or window. Reload to refresh your session.You signed out in another tab or window. Reload to refresh your session.You switched accounts on another tab or window. Reload to refresh your session.Dismiss alert
Due to the way MySQL 5 supports information_schema, the SQL statement used for foreign_key_info requires a full scan of all databases. This can break servers in environments with a large amount of databases (literally die from an OOM), or take a long time to complete in the best case. Even with filters for key_column_usage.constraint_schema, the table_constraints scan isn't filtered properly and results in an EXPLAIN plan like this:
id: 1
select_type: SIMPLE
table: A
type: ALL
possible_keys: NULL
key: TABLE_SCHEMA
key_len: NULL
ref: NULL
rows: NULL
Extra: Using where; Open_full_table; Scanned 1 database
*************************** 2. row ***************************
id: 1
select_type: SIMPLE
table: B
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: NULL
Extra: Using where; Open_full_table; Scanned all databases; Using join buffer (Block Nested Loop)
2 rows in set (0.00 sec)
So, for table B, the optimizer Scanned all databases, which is bad. The table_constraints inclusion is unnecessary, anyway, since all foreign/unique/PK data is available in key_column_usage. Better to just take out the problematic join, and fix the optimization problem at the same time.
The text was updated successfully, but these errors were encountered:
The code could include JOINs to fill in the NULL gaps in the SELECT clause, but those JOIN statements would need to apply the filters directly in order to get the EXPLAIN plan benefit. This is because MySQL 5's implementation of filtering is rather kludgy and doesn't take advantage of the optimizer. For example:
JOIN INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS R ON (
R.CONSTRAINT_SCHEMA = $database AND
R.CONSTRAINT_NAME = A.CONSTRAINT_NAME
)
Due to the way MySQL 5 supports
information_schema
, the SQL statement used forforeign_key_info
requires a full scan of all databases. This can break servers in environments with a large amount of databases (literally die from an OOM), or take a long time to complete in the best case. Even with filters forkey_column_usage.constraint_schema
, thetable_constraints
scan isn't filtered properly and results in an EXPLAIN plan like this:So, for table
B
, the optimizerScanned all databases
, which is bad. Thetable_constraints
inclusion is unnecessary, anyway, since all foreign/unique/PK data is available inkey_column_usage
. Better to just take out the problematic join, and fix the optimization problem at the same time.The text was updated successfully, but these errors were encountered: