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

I think this is exactly the sort of thing that INFORMATION_SCHEMA is intended for.

Answer 2

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.

Answer 3

Using INFORMATION_SCHEMA for this is OK on static or administrative systems but is not recommended for a transactional application function as INFORMATION_SCHEMA is probably implemented as views on top of the native system data dictionary.

This would be a fairly inefficient way to do a generic 'D' operation for a CRUD library. Also, on many systems (Oracle comes to mind) the system data dictionary is actually implemented as views on a lower level data structure. This means that the native system data dictionary may also not be suitable for this either. The system data dictionary may also change from version to version.

There should be relatively few instances where a straight 'delete' of a record and all of its children is the right way to go. Doing this as a generic function may get you little practical beneift. Also, if the foreign keys are not present in the database you will get orphaned children lying about as this approach is dependent on the FK's being present to know which children to delete.

Answer 4

Slows my applications to a crawl, but I need the foreign key constraint data to get everything hooked together properly.

The delays are huge when querying information schema, and make a page that used to load instantly, load in 3-4 seconds.

Well, at least foreign key constraints are available in MySQL 5, that makes for more robust application development, but obviously at a cost.

People have been complaining about this issue since 2006 based on my Google searches, and the problem remains -- must not be an easy fix ;--(

Answer 5

I was looking into this as well. I want to use the KEY_COLUMN_USAGE for some CRUD. And I noticed that there aren't any keys or indexes available on these tables. That could be the reason for poor performance.