Bobcares

KEY_COLUMN_USAGE MySQL | What & Why

PDF Header PDF Footer

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.

PREVENT YOUR SERVER FROM CRASHING!

Never again lose customers to poor server speed! Let us help you.

Our server experts will monitor & maintain your server 24/7 so that it remains lightning fast and secure.

GET STARTED

0 Comments

Submit a Comment

Your email address will not be published. Required fields are marked *

Get featured on the Bobcares blog and share your expertise with a global tech audience.

WRITE FOR US
server management

Spend time on your business, not on your servers.

TALK TO US

Or click here to learn more.

Speed issues driving customers away?
We’ve got your back!