What is the most efficient way to find all foreign keys for a table?

Question

How does one most efficiently get foreign key information (including the update/delete rules)?

The most obvious way is to use something like the following query:

SELECT 
`RC` . `CONSTRAINT_CATALOG` AS `fkCatalog` , 
`RC` . `CONSTRAINT_SCHEMA` AS `fkSchema` , 
`RC` . `CONSTRAINT_NAME` AS `fkName` , 
`RC` . `UPDATE_RULE` AS `onUpdate` ,
`RC` . `DELETE_RULE` AS `onDelete` , 
`RC` . `TABLE_NAME` AS `fkTable` , 
`RC` . `REFERENCED_TABLE_NAME` AS `refTable` , 
`KCU` . `COLUMN_NAME` AS `fkColumn` , 
`KCU` . `REFERENCED_COLUMN_NAME` AS `refColumn` , 
`KCU` . `ORDINAL_POSITION` AS `fkOrdinal` 
FROM 
`INFORMATION_SCHEMA` . `REFERENTIAL_CONSTRAINTS` AS `RC` 
INNER JOIN `INFORMATION_SCHEMA` . `KEY_COLUMN_USAGE` AS `KCU` 
ON 
`KCU` . `CONSTRAINT_SCHEMA` = `RC` . `CONSTRAINT_SCHEMA` AND 
`KCU` . `CONSTRAINT_NAME` = `RC` . `CONSTRAINT_NAME` 
WHERE 
`RC` . `CONSTRAINT_SCHEMA` = ? AND 
`RC` . `TABLE_NAME` = ? 
ORDER BY 
`RC` . `REFERENCED_TABLE_NAME` , `KCU` . `ORDINAL_POSITION`

See for instance answers in:

How to find all tables that have foreign keys that reference particular table.column and have values for those foreign keys?

https://dba.stackexchange.com/questions/102371/how-to-check-foreign-keys-related-to-a-table

How to find all tables that have foreign keys that reference particular table.column and have values for those foreign keys?

But in a moderately sized database (~20GB total size with 1000-10000 tables between ~20 databases), this can take seconds (almost 3 seconds per query on my system).

Is there a more efficient way to fetch them?

Note: I am using MySQL 5.7.

Answer 1

One solution ended up being to explicitly reference the table and schema on both sides of the join. I know that the information_schema tables (especially in MySQL <8.0) aren't tables in the typical sense, so I guess they can't efficient do joins sometimes.

I first tried to reverse the join order, which didn't help, or putting the where on the key_column_usage table instead, which didn't help. Explicitly referencing the schema and table on both sides of the join did the trick though.

In any case, the following query took on average between 1-2ms whereas the one above took just under 3 seconds on average (numbers taken from 1000 or so queries from the performance_schema).

SELECT 
`RC` . `CONSTRAINT_CATALOG` AS `fkCatalog` , 
`RC` . `CONSTRAINT_SCHEMA` AS `fkSchema` , 
`RC` . `CONSTRAINT_NAME` AS `fkName` , 
`RC` . `UPDATE_RULE` AS `onUpdate` , 
`RC` . `DELETE_RULE` AS `onDelete` , 
`RC` . `TABLE_NAME` AS `fkTable` , 
`RC` . `REFERENCED_TABLE_NAME` AS `refTable` , 
`KCU` . `COLUMN_NAME` AS `fkColumn` , 
`KCU` . `REFERENCED_COLUMN_NAME` AS `refColumn` , 
`KCU` . `ORDINAL_POSITION` AS `fkOrdinal` 
FROM 
`INFORMATION_SCHEMA` . `KEY_COLUMN_USAGE` AS `KCU` 
INNER JOIN 
`INFORMATION_SCHEMA` . `REFERENTIAL_CONSTRAINTS` AS `RC` 
ON 
`KCU` . `CONSTRAINT_SCHEMA` = `RC` . `CONSTRAINT_SCHEMA` AND 
`KCU` . `CONSTRAINT_NAME` = `RC` . `CONSTRAINT_NAME` 
WHERE 
`KCU` . `TABLE_SCHEMA` = ? AND -- The addition of this and the next row are the only real difference.
`KCU` . `TABLE_NAME` = ? AND 
`RC` . `CONSTRAINT_SCHEMA` = ? AND 
`RC` . `TABLE_NAME` = ? 
ORDER BY 
`KCU` . `REFERENCED_TABLE_NAME` , `KCU` . `ORDINAL_POSITION`

Answer 2

This will give you all the information about foreign key

there a lot more information, leke referencd column and refrencd table

SELECT 
   TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME, CONSTRAINT_NAME
   FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE
   WHERE REFERENCED_TABLE_SCHEMA IS NOT NULL;

in addition there is in mysql 5.7 has this option but only for innodb tables

 SELECT * FROM INFORMATION_SCHEMA.INNODB_SYS_FOREIGN

Which gives you a type

TYPE A collection of bit flags with information about the foreign key column, ORed together. 1 = ON DELETE CASCADE, 2 = ON UPDATE SET NULL, 4 = ON UPDATE CASCADE, 8 = ON UPDATE SET NULL, 16 = ON DELETE NO ACTION, 32 = ON UPDATE NO ACTION.

http://mysql.babo.ist/#/en/innodb-sys-foreign-table.html