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.