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
Related posts:
Posts
![IVRAMANA](https://draft.blogger.com/img/profile_blank_gray.jpg" title="IVRAMANA)
0 comments:
Post a Comment