Show indexes for a table with the MySQL INFORMATION_SCHEMA

I’ve previously posted how to get the indexes for a MySQL table using a "SHOW INDEXES" SQL query and in this post show an alternative way to get the indexes for a table using MySQL’s INFORMATION_SCHEMA.

Example tables

The examples in this post uses a table structured as follows:

CREATE TABLE `products` (
  `product_id` int(10) unsigned NOT NULL auto_increment,
  `url` varchar(100) NOT NULL,
  `name` varchar(50) NOT NULL,
  `description` varchar(255) NOT NULL,
  `price` decimal(10,2) NOT NULL,
  `visible` tinyint(1) unsigned NOT NULL default '1',
  PRIMARY KEY  (`product_id`),
  UNIQUE KEY `url` (`url`),
  KEY `visible` (`visible`)
)

Get the indexes

This first query will get all the column name and the index type for all columns that are indexed, from the "test" database and "products" table:

SELECT COLUMN_NAME, COLUMN_KEY
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = 'test'
AND TABLE_NAME = 'products'
AND COLUMN_KEY != ''

For the example table this will return the following:

+-------------+------------+
| COLUMN_NAME | COLUMN_KEY |
+-------------+------------+
| product_id  | PRI        |
| url         | UNI        |
| visible     | MUL        |
+-------------+------------+

PRI indicates it is a primary key; UNI that it is indexed with a unique constraint; and MUL that it is a regular index.

Get the primary key(s)

The second example shows how to just get the primary keys from a table using the INFORMATION_SCHEMA, using the example "products" table from the "test" database again:

SELECT COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = 'test'
AND TABLE_NAME = 'products'
AND COLUMN_KEY = 'PRI'

For the example table this would return:

+-------------+
| COLUMN_NAME |
+-------------+
| product_id  |
+-------------+