foreign_key_info is un-optimized for large sets of databases

[SineSwiper]

trafficstars

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.

[SineSwiper]

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
)

Not R.CONSTRAINT_SCHEMA = A.CONSTRAINT_SCHEMA.

Anyway, I can provide a PR if you like.