0
SELECT * 
FROM information_schema.TABLE_CONSTRAINTS 
WHERE CONSTRAINT_SCHEMA = DATABASE() 
  AND CONSTRAINT_NAME   = 'FK_FOLDER_FILTER_CONTRACT_CATEGORY_CONTRACT_CATEGORY' 
  AND CONSTRAINT_TYPE   = 'FOREIGN KEY';

The above query takes around 2 mins to execute is there any other alternate query or how can we optimize this query

  • 1
    information_schema.TABLE_CONSTRAINTS is a view. Get its text, insert into your query, then simplify it by removing unnesessary parts. For example, it contains UNION which seems to be excess in your case (and a half of a query), you do not need to execute access rights checking function calls, and so on. – Akina Feb 26 at 7:21
  • Which version of MySQL? There was a huge change starting with 8.0. Also, how many tables in your dataset? – Rick James Mar 3 at 2:58
0

information_schema isn't a real database. To preform this query it needs to open tables.

The internal code for information_schema isn't optimized and it will frequently generate the data for the entire table requested without consideration of the query. As such your query could be opening every table regardless of the current table.

| improve this answer | |
  • 1
    Your answer applies to versions before 8.0. – Rick James Mar 3 at 3:00
0

For grins, I EXPLAINed your query on 5.7:

+----+-------------+-------------------+------------+------+---------------+------+---------+------+------+----------+-----------------------------------------------------+
| id | select_type | table             | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra                                               |
+----+-------------+-------------------+------------+------+---------------+------+---------+------+------+----------+-----------------------------------------------------+
|  1 | SIMPLE      | TABLE_CONSTRAINTS | NULL       | ALL  | NULL          | NULL | NULL    | NULL | NULL |     NULL | Using where; Open_full_table; Scanned all databases |
+----+-------------+-------------------+------------+------+---------------+------+---------+------+------+----------+-----------------------------------------------------+

And 8.0:

+----+--------------+--------------+------------+--------+--------------------+-------------+---------+---------------------+------+----------+----------------------------------------------------+
| id | select_type  | table        | partitions | type   | possible_keys      | key         | key_len | ref                 | rows | filtered | Extra                                              |
+----+--------------+--------------+------------+--------+--------------------+-------------+---------+---------------------+------+----------+----------------------------------------------------+
|  1 | PRIMARY      | <derived2>   | NULL       | ref    | <auto_key0>        | <auto_key0> | 424     | const,const,const   |   10 |   100.00 | NULL                                               |
|  2 | DERIVED      | cat          | NULL       | index  | PRIMARY            | name        | 194     | NULL                |    1 |   100.00 | Using index                                        |
|  2 | DERIVED      | idx          | NULL       | ALL    | table_id           | NULL        | NULL    | NULL                |  244 |    40.00 | Using where; Using join buffer (Block Nested Loop) |
|  2 | DERIVED      | tbl          | NULL       | eq_ref | PRIMARY,schema_id  | PRIMARY     | 8       | mysql.idx.table_id  |    1 |   100.00 | Using where                                        |
|  2 | DERIVED      | sch          | NULL       | eq_ref | PRIMARY,catalog_id | PRIMARY     | 8       | mysql.tbl.schema_id |    1 |   100.00 | Using where                                        |
|  3 | UNION        | cat          | NULL       | index  | PRIMARY            | name        | 194     | NULL                |    1 |   100.00 | Using index                                        |
|  3 | UNION        | fk           | NULL       | index  | table_id           | table_id    | 202     | NULL                |   63 |   100.00 | Using index; Using join buffer (Block Nested Loop) |
|  3 | UNION        | tbl          | NULL       | eq_ref | PRIMARY,schema_id  | PRIMARY     | 8       | mysql.fk.table_id   |    1 |   100.00 | Using where                                        |
|  3 | UNION        | sch          | NULL       | eq_ref | PRIMARY,catalog_id | PRIMARY     | 8       | mysql.tbl.schema_id |    1 |   100.00 | Using where                                        |
|  4 | UNION        | cc           | NULL       | ALL    | table_id           | NULL        | NULL    | NULL                |    1 |   100.00 | NULL                                               |
|  4 | UNION        | cat          | NULL       | index  | PRIMARY            | name        | 194     | NULL                |    1 |   100.00 | Using index; Using join buffer (Block Nested Loop) |
|  4 | UNION        | tbl          | NULL       | eq_ref | PRIMARY,schema_id  | PRIMARY     | 8       | mysql.cc.table_id   |    1 |   100.00 | Using where                                        |
|  4 | UNION        | sch          | NULL       | eq_ref | PRIMARY,catalog_id | PRIMARY     | 8       | mysql.tbl.schema_id |    1 |   100.00 | Using where                                        |
| NULL | UNION RESULT | <union2,3,4> | NULL       | ALL    | NULL               | NULL        | NULL    | NULL                | NULL |     NULL | Using temporary                                    |
+----+--------------+--------------+------------+--------+--------------------+-------------+---------+---------------------+------+----------+----------------------------------------------------+
14 rows in set, 1 warning (0.01 sec)

The old code needs to open all the tables, so it is slow if you have lots of tables.

The 8.0 code fetches from InnoDB tables, but goes through lots of hoops.

The number of tables is not the same, so I could not make a valid comparison, not do I know how many tables you have.

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.