Performant Way to List Foreign Keys for a MySQL Table?

Is there a performant way to fetch the list of foreign keys assigned to a MySQL table?

Querying the information schema with

SELECT
    `column_name`, 
    `referenced_table_schema` AS foreign_db, 
    `referenced_table_name` AS foreign_table, 
    `referenced_column_name`  AS foreign_column 
FROM
    `information_schema`.`KEY_COLUMN_USAGE`
WHERE
    `constraint_schema` = SCHEMA()
AND
    `table_name` = 'your-table-name-here'
AND
    `referenced_column_name` IS NOT NULL
ORDER BY
    `column_name`;

works, but is painfully slow on the versions of MySQL I've tried it with. A bit of research turned up this bug, which seems to indicate it's an ongoing issue without a clear solution. Th开发者_开发问答e solutions which are hinted at require reconfiguring or recompiling mysql with a patch, which doesn't work for the project I'm working on.

I realize it's possible to issue the following

SHOW CREATE TABLE table_name;

and get a string representation of a CREATE TABLE statement, which will include the foreign key constraints. However, parsing this string seems like it would be fragile, and I don't have a large corpus of CREATE TABLE statements to test against. (if there's a standard bit of parsing code for this out there, I'd love some links)

I also realize I can list the indexes with the following

SHOW CREATE TABLE table_name;

The list of indexes will include the foreign keys, but there doesn't appear to be a way to determine which of the indexes are foreign keys, and which are "regular" MySQL indexes. Again, some cross referencing with the SHOW CREATE table information could help here, but that brings us back to fragile string parsing.

Any help, or even links to other smart discussions on the issue, would be appreciated.

SequelPro and Magento both utilize the SHOW CREATE TABLE query to load the foreign key information. Magento's implementation is the one I am going to reference since it's both a PHP based system and one that both of us are very familiar with. However, the following code snippets can be applied to any PHP based system.

The parsing is done in the Varien_Db_Adapter_Pdo_Mysql::getForeignKeys() method (the code for this class can be found here) using a relatively simple RegEx:

    $createSql = $this->getCreateTable($tableName, $schemaName);

    // collect CONSTRAINT
    $regExp  = '#,\s+CONSTRAINT `([^`]*)` FOREIGN KEY \(`([^`]*)`\) '
        . 'REFERENCES (`[^`]*\.)?`([^`]*)` \(`([^`]*)`\)'
        . '( ON DELETE (RESTRICT|CASCADE|SET NULL|NO ACTION))?'
        . '( ON UPDATE (RESTRICT|CASCADE|SET NULL|NO ACTION))?#';
    $matches = array();
    preg_match_all($regExp, $createSql, $matches, PREG_SET_ORDER);
    foreach ($matches as $match) {
        $ddl[strtoupper($match[1])] = array(
            'FK_NAME'           => $match[1],
            'SCHEMA_NAME'       => $schemaName,
            'TABLE_NAME'        => $tableName,
            'COLUMN_NAME'       => $match[2],
            'REF_SHEMA_NAME'    => isset($match[3]) ? $match[3] : $schemaName,
            'REF_TABLE_NAME'    => $match[4],
            'REF_COLUMN_NAME'   => $match[5],
            'ON_DELETE'         => isset($match[6]) ? $match[7] : '',
            'ON_UPDATE'         => isset($match[8]) ? $match[9] : ''
        );
    }

In the doc block it describes the resulting array as follows:

    /**
     * The return value is an associative array keyed by the UPPERCASE foreign key,
     * as returned by the RDBMS.
     *
     * The value of each array element is an associative array
     * with the following keys:
     *
     * FK_NAME          => string; original foreign key name
     * SCHEMA_NAME      => string; name of database or schema
     * TABLE_NAME       => string;
     * COLUMN_NAME      => string; column name
     * REF_SCHEMA_NAME  => string; name of reference database or schema
     * REF_TABLE_NAME   => string; reference table name
     * REF_COLUMN_NAME  => string; reference column name
     * ON_DELETE        => string; action type on delete row
     * ON_UPDATE        => string; action type on update row
     */

I know it's not exactly what you were asking for since it's using the SHOW CREATE TABLE output, but based on my findings, it seems to be the generally accepted way of doing things.