KEY_COLUMN_USAGE MySQL | What & Why

KEY_COLUMN_USAGE MySQL giving you trouble? Find out about the columns in this table from the experts at Bobcares. 

At Bobcares, we offer solutions for every query, big and small, as a part of our Server Management Services.

Let’s take a look at how our Support Team is ready to help customers learn about the KEY_COLUMN_USAGE in MySQL.

What is KEY_COLUMN_USAGE in MySQL?

The KEY_COLUMN_USAGE view helps us determine which key columns currently have constraints. However, this particular table does not offer information regarding functional key parts as they are expressions.

Furthermore, the KEY_COLUMN_USAGE identifies columns in the database that are under restriction due to a unique foreign key or primary key constraint. Now, let’s have a look at the columns in the KEY_COLUMN_USAGE table:

• CONSTRAINT_CATALOG: The name of the database that has the constraint belongs.
• CONSTRAINT_NAME: The name of the constraint.
• TABLE_CATALOG: The name of the database that contains the table with the column restricted by this constraint.
• CONSTRAINT_SCHEMA: The name of the schema to which the constraint belongs.
• TABLE_NAME: The name of the table containing the constraint.
• TABLE_SCHEMA: The name of the schema containing the table.
• COLUMN_NAME: The name of the column that contains the constraint.
• REFERENCED_TABLE_NAME: The table referenced by the constraint.
• POSITION_IN_UNIQUE_CONSTRAINT NULL: This column is for unique & primary-key constraints. Furthermore, in the case of foreign-key constraints, it is the ordinal position in the key of the table we are referencing.
• ORDINAL_POSITION: This indicates the column’s position in the constraint.
• REFERENCED_TABLE_SCHEMA: The schema referenced by the constraint.
• REFERENCED_COLUMN_NAME: The column referenced by the constraint.

About the columns in the KEY_COLUMN_USAGE table

Additionally, we can access information about the columns in the KEY_COLUMN_USAGE view with this command:

USE information_schema;
DESC key_column_usage;

In short, we come across the following output:

+-------------------------------+--------------+------+------+---------+-------+
| Field                         | Type         | Null | Key  | Default | Extra |
+-------------------------------+--------------+------+------+---------+-------+
| CONSTRAINT_CATALOG            | varchar(512) | NO   |      | NULL    |       |
| CONSTRAINT_SCHEMA             | varchar(64)  | NO   |      | NULL    |       |
| CONSTRAINT_NAME               | varchar(64)  | NO   |      | NULL    |       |
| TABLE_CATALOG                 | varchar(512) | NO   |      | NULL    |       |
| TABLE_SCHEMA                  | varchar(64)  | NO   |      | NULL    |       |
| TABLE_NAME                    | varchar(64)  | NO   |      | NULL    |       |
| COLUMN_NAME                   | varchar(64)  | NO   |      | NULL    |       |
| ORDINAL_POSITION              | bigint(10)   | NO   |      | NULL    |       |
| POSITION_IN_UNIQUE_CONSTRAINT | bigint(10)   | YES  |      | NULL    |       |
| REFERENCED_TABLE_SCHEMA       | varchar(64)  | YES  |      | NULL    |       |
| REFERENCED_TABLE_NAME         | varchar(64)  | YES  |      | NULL    |       |
| REFERENCED_COLUMN_NAME        | varchar(64)  | YES  |      | NULL    |       |
+-------------------------------+--------------+------+------+---------+-------+
12 rows in set (0.00 sec)

For instance, suppose we have two tables with the following definitions:

CREATE TABLE t1
(
    s1 INT,
    s2 INT,
    PRIMARY KEY(s1)
) ENGINE=InnoDB;

CREATE TABLE t2
(
    s1 INT,
    s2 INT,
    KEY(s2),
    CONSTRAINT CO FOREIGN KEY (s1) REFERENCES t1(s2)
) ENGINE=InnoDB;

In this scenario, the KEY_COLUMN_USAGE view has two rows:

• One row with CONSTRAINT_NAME = ‘PRIMARY’, TABLE_NAME = ‘t1’, COLUMN_NAME = ‘s1’, ORDINAL_POSITION = 1, POSITION_IN_UNIQUE_CONSTRAINT = NULL.
• One row with CONSTRAINT_NAME = ‘CO’, TABLE_NAME = ‘t2’, COLUMN_NAME = ‘s2’, ORDINAL_POSITION = 2, POSITION_IN_UNIQUE_CONSTRAINT = 1.

Furthermore, if you have been performance issues with INFORMATION_SCHEMA KEY_COLUMN_USAGE, our Support Techs have an easy fix. Fortunately, all we have to do is use GLOBAL variable as seen below:

SET global innodb_stats_on_metadata =0;

In this scenario, we can run the query as:

set global innodb_stats_on_metadata =0;
Query OK, 0 rows affected (0.00 sec)
mysql> SELECT REFERENCED_TABLE_NAME,TABLE_NAME,COLUMN_NAME,CONSTRAINT_SCHEMA
   -> FROM INFORMATION_SCHEMA.key_column_usage;

As a result, we will get the output faster, and put poor performance issues to rest. In fact, one of our customers saw a massive decrease in output time from 8 seconds to 0.25 seconds with this handy fix by our skilled Support Engineers.

However, if you are still facing trouble with this table view, drop a line in our chat box. Our Support Techs are here to help sort out your problem in a jiffy.

[Looking for a solution to another query? We are just a click away.]

Conclusion

To conclude, our skilled Support Engineers at Bobcares gave us an introduction to KEY_COLUMN_USAGE in MySQL.