information_schema is the need for standardised database metadata queries. Unfortunately, queries against the MySQL information_schema can be painfully slow, and MySQL AB spokespersons have told us not to expect a fix any time soon. We solved the problem in theUsual by writing a function that can either query information_schema or parse the results of iterative SHOW TABLES commands. The SHOW TABLES method is 10-50 times faster depending on the environment. This version looks for child tables in one database; it is a bit slower when modified to search all server DBs, but even then it is much faster than an information_schema query. It assumes an available connection object $conn:
<?php
function childtables( $db, $table, $via_infoschema=FALSE ) {
GLOBAL $conn;
$ret = array();
if( $via_infoschema ) {
$res = mysqli_query( childtablesqry( $conn, $db, $table )) || die( mysqli_error($conn) );
if( !is_bool( $res ))
while( $row = mysqli_fetch_row( $res ))
$ret[] = $row;
}
else {
$tables = array();
$res = mysqli_query( $conn, "SHOW TABLES" );
while( $row = mysqli_fetch_row( $res )) $tables[] = $row[0];
foreach( $tables as $referringtbl ) {
$res = mysqli_query( "SHOW CREATE TABLE $referringtbl" );
// SHOW CREATE TABLE returns 2 cols: tblname, create stmt
$row = mysqli_fetch_row( $res );
$s = " REFERENCES "$table" ("";
if(( $start = stripos( $row[1], $s )) > 0 ) {
$end = strpos( $row[1], '"', $start + strlen( $s ));
$referencedcol = substr( $row[1], $start+strlen($s), $end-$start-strlen($s) );
$s = "FOREIGN KEY ("";
if(( $start = stripos( $row[1], $s )) > 0 ) {
$end = strpos( $row[1], '"', $start + strlen( $s ));
$referencingcol = substr( $row[1], $start+strlen($s), $end-$start-strlen($s) );
}
// array cols: dbname, table_name,
// referencing_column_name, referenced_column_name
$ret[] = array( $db, $referringtbl, $referencingcol, $referencedcol );
}
}
}
return $ret;
}
function childtablesqry( $db, $table ) {
return "SELECT c.table_schema,u.table_name,u.column_name,u.referenced_column_name " .
"FROM information_schema.table_constraints AS c " .
"INNER JOIN information_schema.key_column_usage AS u " .
"USING( constraint_schema, constraint_name ) " .
"WHERE c.constraint_type = 'FOREIGN KEY' " .
"AND u.referenced_table_schema='$db' " .
"AND u.referenced_table_name = '$table' " .
"ORDER BY c.table_schema,u.table_name";
}
?>
Last updated 27 Feb 2025
