The MySQL/MariaDB ‘SHOW INDEX’ Statement, With Examples

This article shows you how to use the MySQL SHOW INDEX statement to list the details of indexes and keys in a table.

To use the SHOW INDEX statement, you will need to know which database and table you wish to view index information for.

Looking to list the databases and tables on your system?

What Are Indexes?

Indexes are a tool that allows a database to quickly lookup data in a table for a certain column.

Usually, when searching a table, the database software must read every row in the table to find rows matching the search conditions.

Indexes store which values are stored for a column in each row, along with that row’s position – allowing for fast lookups of values in that column.

This has its own overheads, requiring additional space and memory, so it isn’t done for every column.

It is a feature commonly enabled for columns that require fast lookup or commonly searched fields. This is often the id column in a table so that rows can be found quickly by a unique identifier rather than reading the whole table looking for a row.

Usernames are also frequently indexed to make logging in less resource-intensive – with an index; the whole table doesn’t have to be searched for a matching username every time a user logs in, saving system resources.

Why Would You Want To Show Indexes?

If you plan to modify a table or insert data into it, you should know the structure of the table in advance.

If an indexed column requires uniqueness, it’s pointless trying to insert data that includes duplicate data in that column – so knowing ahead of time that duplicates aren’t allowed will save time and effort.

If trying to modify a table, you may want to avoid removing indexed or unique columns with important information in them – or the previous developer may have left some important comments with the index that you should be aware of.

MySQL SHOW INDEX Syntax

The SHOW INDEX statement will list all indexes’ details (including primary keys) for a specified table. The syntax is as follows:

SHOW [EXTENDED] INDEX FROM database_name.table_name [WHERE EXPRESSION]

Note that:

• The EXTENDED keyword is optional and will show information on indexes that are created for MySQL’s internal use
  • You probably don’t need to see this information unless you’re debugging MySQL itself or in some serious trouble
• The INDEX keyword can be replaced with the words INDEXES or KEYS
  • They’ll all do the same thing; it’s just semantics/preference
• Similarly, the FROM keyword can be replaced with IN if you prefer
• database-name and the following period (.) are optional if you are already working inside the database which contains the table you wish to view index information for
• An optional WHERE clause can be added to limit the returned results to matching rows
  • This WHERE clause applies to the columns returned by SHOW INDEX, not for the table itself!

Example – Showing All Indexes

Consider the following table:

CREATE TABLE IF NOT EXISTS `countries` (
    `id` int(6) unsigned NOT NULL,
    `country_code` varchar(2) NOT NULL,
    `country_name` varchar(200) NOT NULL,
    PRIMARY KEY (`id`),
    INDEX(`country_code`)
) DEFAULT CHARSET=utf8;

…it contains three columns:

• id – a field containing an integer value which is set to the PRIMARY KEY for the table
  • The primary key is the primary unique identifier for rows in a table
• country_code – an indexed varchar (string) field containing the country code for fast lookup
• country name – a varchar field with no indexing

By simply viewing this table’s contents, we wouldn’t know any of the above, of course.

To find out details of the index fields, we could run:

SHOW INDEX FROM `countries`;

Which would output:

The format of this output will vary depending on your version of MySQL – it may be presented as a list rather than in a tabulated format.

From the above output, we can determine that we must ensure that values in the id field are unique when inserting information. We also know that when querying the table, the id and country_code can quickly retrieve matching rows without having to search the whole table, which may be quite long and time-intensive.

Example – Querying Indexes

This example adds a WHERE clause to limit the returned results – in this case, only information on indexed columns which are not unique are returned:

SHOW INDEX FROM `countries` WHERE `Non_unique` = 1;

Which would return: