Brian Cryer

 

Cry MySQL How to...

List all the indexes on a table


To list all of the indexes on a given table use:

show indexes from mytable

where 'mytable' is the name of the table for which you want to see the indexes.

What this will return is a table listing all the columns which form part of an index together with the index name, with one row per column per index.

For example:

show indexes from mytable

might generate (and I am only including a subset of the columns that show-indexes will return):

+---------+------------+----------+--------------+-------------+---
| Table   | Non_unique | Key_name | Seq_in_index | Column_name | ..
+---------+------------+----------+--------------+-------------+---
| mytable | 0          | PRIMARY  | 1            | Reference   |
| mytable | 0          | PRIAMRY  | 2            | PageID      | ..
| mytable | 1          | I1       | 1            | ClientID    |
+---------+------------+----------+--------------+-------------+---

This shows that there are two indexes - "PRIMARY" (the primary key) and an index called "I1". Where "PRIMARY" is composed of two columns "Reference" and "PageID", in that order and "I1" is a non-unique index on the field "ClientID".


These notes have been tested against MySQL version 5, and may apply to other versions as well.



About the author: is a dedicated software developer and webmaster. For his day job he develops websites and desktop applications as well as providing IT services. He moonlights as a technical author and consultant.