Databases
StackOverflow2013
Postsdbo
POIs querying the MySQL information_schema database a good way to find related tables?
Body
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POIs querying the MySQL information_schema database a good way to find related tables?
    text
    copied!<p>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. </p> <p>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:</p> <pre><code>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') </code></pre> <p>which works perfectly for determining the tables which I need to search, however it is <em>very</em> 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.</p> <p>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.</p>
 

Querying!

 
Guidance
An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload