Is querying the MySQL information_schema database a good way to find related tables?

Question

I have a table which is referenced by foreign keys on many other tables. In my program if I want to delete one of these rows I need to first search for dependencies and present them to the user - "This object depends on x from table y, z from table q, etc". I also expect the number of tables which have foreign keys to this table to grow considerably over time.

Is the information_schema database a good way to do a search for all dependencies? I tried to query it to retrieve a list of all tables which have foreign keys to my table, then iterate over the result and select all entries from each table where the foreign key value matches the value the user is trying to delete. The query I have is as follows:

SELECT * FROM `KEY_COLUMN_USAGE` kcu
LEFT JOIN TABLE_CONSTRAINTS tc
ON tc.CONSTRAINT_NAME = kcu.CONSTRAINT_NAME
WHERE tc.CONSTRAINT_TYPE='FOREIGN KEY'
AND (kcu.REFERENCED_TABLE_SCHEMA='db')
AND (kcu.REFERENCED_TABLE_NAME = 'testtable')

which works perfectly for determining the tables which I need to search, however it is very slow. The query takes around 1 to 2 seconds at best to execute on my development machine, which will reduce a lot when I run it on my production server, but will still be quite slow.

I need to know if it's a bad idea to use information_schema in this way. If not, how I can extract better performance from the query. Is the query I'm using solid or is there a better way to do it? If so, how best should I tackle this problem from a maintainability perspective.

Answer 1

Dvorak is right, INFORMATION_SCHEMA is intended for that.

Concerning your performance concerns, there are several ways you can improve the performance

• Easy way, but not much improvement will come from it: Store the info in a static variable. At least the query will occur only once per page

• Use persistent caching : The alternative PHP cache can help you (see http://fr3.php.net/manual/en/book.apc.php). The info you'll get from the information schema is a good candidate to store in a persistent cache.

• Use a ORM library, such as doctrine (http://www.doctrine-project.org/) A look at the file lib/Doctrine/Import/Mysql.php will show that it does exactly what you need, and much more.