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 contains 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 is 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 a 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.