Home | About Chris Hope | Contact Chris Hope | Search:

Show indexes for a table with MySQL

Posted April 15th, 2009 in MySql

MySQL has a SQL query "SHOW INDEX FROM" which returns the indexes from a table. This post looks at some example usage of this query to get a list of indexes and a list of primary keys for a table with MySQL.

Example tables

The examples in this post have two tables which are structued 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`)
)

CREATE TABLE `products_to_categories` (
  `product_id` int(10) unsigned NOT NULL,
  `category_id` int(10) unsigned NOT NULL,
  PRIMARY KEY  (`product_id`,`category_id`)
)

Show the indexes

Running the following query will show all the indexes for the products table:

SHOW INDEXES FROM products;

This will return the following:

+----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| Table    | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| products |          0 | PRIMARY  |            1 | product_id  | A         |           0 |     NULL | NULL   |      | BTREE      |         |
| products |          0 | url      |            1 | url         | A         |           0 |     NULL | NULL   |      | BTREE      |         |
| products |          1 | visible  |            1 | visible     | A         |           0 |     NULL | NULL   |      | BTREE      |         |
+----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+

Show the primary keys

To just show the primary key(s) for the products table run this query:

SHOW INDEXES FROM products WHERE Key_name = "PRIMARY";

This will return the following:

+----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| Table    | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| products |          0 | PRIMARY  |            1 | product_id  | A         |           0 |     NULL | NULL   |      | BTREE      |         |
+----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+

Show the primary keys - multiple primary keys

The final example uses the products_to_categories table which has a multi-part primary key, so two rows are returned. The SQL:

SHOW INDEXES FROM products_to_categories WHERE Key_name = "PRIMARY";

And the result:

+------------------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| Table                  | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+------------------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| products_to_categories |          0 | PRIMARY  |            1 | product_id  | A         |        NULL |     NULL | NULL   |      | BTREE      |         |
| products_to_categories |          0 | PRIMARY  |            2 | category_id | A         |           0 |     NULL | NULL   |      | BTREE      |         |
+------------------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+

Summary

It's easy to show the indexes and primary keys for a table with MySQL. You can read more about the columns returned on the SHOW INDEX Syntax manual page in the MySQL manual.

Comments

blog comments powered by Disqus