MariaDB Show Indexes

Summary: in this tutorial, you will learn how to use the MariaDB show indexes statement to find all indexes from a table.

Introduction to MariaDB show indexes statement

The show indexes statement allows you to query indexes from a table.

To show indexes associated with a table, you use the following statement:

show indexes from table_name;
Code language: SQL (Structured Query Language) (sql)

In this syntax, you specify the name of the table from which you want to show the indexes.

If you are not connected to any specific database, you can specify the database name in the show indexes statement as shown int he the following command:

show indexes from table_name
in database_name;
Code language: SQL (Structured Query Language) (sql)

Or:

show indexes from database_name.table_name;
Code language: SQL (Structured Query Language) (sql)

In the show indexes statement, you can use the index or keys keywords instead of indexes keyword, and the in keyword instead of the from keyword, for example:

show keys in database_name.table_name;
Code language: SQL (Structured Query Language) (sql)

or

show index from database_name.table_name;Code language: SQL (Structured Query Language) (sql)

To specify a search condition for indexes, you add a where clause to the show indexes statement as follows:

show indexes from table_name
where search_expression;
Code language: SQL (Structured Query Language) (sql)

The show indexes returns the following information on indexes:

• table: is the name of the table to which the index belongs.
• non_unique 1 if the index can have duplicates, 0 if it cannot.
• key_name represents the name of the index. Note that a primary key index always has the name of PRIMARY.
• seq_in_index shows column sequence number in the index. The first column sequence number starts from 1.
• column_name the name of the indexed column.
• collation determines how the values in the indexed column are sorted, A for ascending, B for descending, or null for no sort.
• cardinality returns the estimated number of unique values in the index.
• sub_part represents the index prefix. If the entire column is indexed, it will be null. Otherwise, it shows the number of indexed characters in case the column is partially indexed.
• packed shows how the key is packed; null if it isn’t.
• null if the column can contain null, it is Yes. otherwise, it is blank.
• index_type represents the type of the index such as btree.
• comment shows the information on the index which is not described in its own column.
• index_comment displays the comment of the index specified when you created the index with the comment attribute.

MariaDB show indexes examples

We’ll use the countries from the nation sample database to demonstrate the show indexes statement:

A) Showing all indexes from a table example

The following example uses the show indexes statement to find all indexes from the countries table:

show indexes from countries;
Code language: SQL (Structured Query Language) (sql)

B) Showing some indexes from a table example

The following example uses the show indexes statement to find only unique indexes of the countries table:

show indexes from countries
where non_unique = 0;
Code language: SQL (Structured Query Language) (sql)

In this tutorial, you learned how to use the MariaDB show indexes statement to query indexes from a table.