MySQL SHOW INDEXES
Summary: in this tutorial, you will learn how to query index information from a table by using the MySQL SHOW INDEXES command.
Introduction to MySQL SHOW INDEXES command
To query the index information of a table, you use the SHOW INDEXES statement as follows:
SHOW INDEXES FROM table_name;
Code language: SQL (Structured Query Language) (sql)
To get the index of a table, you specify the table name after the FROM keyword. The statement will return the index information associated with the table in the current database.
You can specify the database name if you are not connected to any database or you want to get the index information of a table in a different database:
SHOW INDEXES FROM table_name
IN database_name;
Code language: SQL (Structured Query Language) (sql)
The following query is similar to the one above:
SHOW INDEXES FROM database_name.table_name;
Code language: SQL (Structured Query Language) (sql)
Note that INDEX and KEYS are the synonyms of the INDEXES, IN is the synonym of the FROM, therefore, you can use these synonyms in the SHOW INDEXES column instead. For example:
SHOW INDEX IN table_name
FROM database_name;
Code language: SQL (Structured Query Language) (sql)
Or
SHOW KEYS FROM tablename
IN databasename;Code language: SQL (Structured Query Language) (sql)
The SHOW INDEXES returns the following information:
table
The name of the table
non_unique
1 if the index can contain duplicates, 0 if it cannot.
key_name
The name of the index. The primary key index always has the name of PRIMARY.
seq_in_index
The column sequence number in the index. The first column sequence number starts from 1.
column_name
The column name
collation
Collation represents how the column is sorted in the index. A means ascending, B means descending, or NULL means not sorted.
cardinality
The cardinality returns an estimated number of unique values in the index.
Note that the higher the cardinality, the greater the chance that the query optimizer uses the index for lookups.
sub_part
The index prefix. It is null if the entire column is indexed. Otherwise, it shows the number of indexed characters in case the column is partially indexed.
packed
indicates how the key is packed; NUL if it is not.
null
YES if the column may contain NULL values and blank if it does not.
index_type
represents the index method used such as BTREE, HASH, RTREE, or FULLTEXT.
comment
The information about the index not described in its own column.
index_comment
shows the comment for the index specified when you create the index with the COMMENT attribute.
visible
Whether the index is visible or invisible to the query optimizer or not; YES if it is, NO if not.
expression
If the index uses an expression rather than column or column prefix value, the expression indicates the expression for the key part and also the column_name column is NULL.
Filter index information
To filter index information, you use a WHERE clause as follows:
SHOW INDEXES FROM table_name
WHERE condition;
Code language: SQL (Structured Query Language) (sql)
You can use any information returned by the SHOW INDEXES statement to filter the index information. For example, the following statement returns only the invisible indexes of a table:
SHOW INDEXES FROM table_name
WHERE VISIBLE = 'NO';
Code language: SQL (Structured Query Language) (sql)
MySQL SHOW INDEXES examples
We will create a new table named contacts to demonstrate the SHOW INDEXES command:
CREATE TABLE contacts(
contact_id INT AUTO_INCREMENT,
first_name VARCHAR(100) NOT NULL,
last_name VARCHAR(100) NOT NULL,
email VARCHAR(100),
phone VARCHAR(20),
PRIMARY KEY(contact_id),
UNIQUE(email),
INDEX phone(phone) INVISIBLE,
INDEX name(first_name, last_name) comment 'By first name and/or last name'
);
Code language: SQL (Structured Query Language) (sql)
The following command returns all index information from the contacts table:
SHOW INDEXES FROM contacts;
Code language: SQL (Structured Query Language) (sql)
The output is:
To get the invisible indexes of the contacts table, you add a WHERE clause as follows:
SHOW INDEXES FROM contacts
WHERE visible = 'NO';
Code language: SQL (Structured Query Language) (sql)
Here is the output:
In this tutorial, you have learned how to use the MySQL SHOW INDEXES statement to get the index information of a table.