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.
