SHOW {INDEX | INDEXES | KEYS} FROM tbl_name [FROM db_name] [WHERE expr]
SHOW INDEX
returns table index information. The format resembles that of the SQLStatistics call in ODBC.
You can use db_name.tbl_name
as an alternative to the tbl_name FROM db_name
syntax. These two statements are equivalent:
SHOW INDEX FROM mytable FROM mydb; SHOW INDEX FROM mydb.mytable;
SHOW KEYS
and SHOW INDEXES
are synonyms for SHOW INDEX
.
You can also list a table's indexes with the following command:
mysqlshow -k db_name tbl_name
See mysqlshow
for more details.
The information_schema.STATISTICS
table stores similar information.
The following fields are returned by SHOW INDEX
.
Field | Description |
---|---|
Table |
Table name |
Non_unique |
1 if the index permits duplicate values, 0 if values must be unique. |
Key_name |
Index name. The primary key is always named PRIMARY . |
Seq_in_index |
The column's sequence in the index, beginning with 1 . |
Column_name |
Column name. |
Collation |
Either A , if the column is sorted in ascending order in the index, or NULL if it's not sorted. |
Cardinality |
Estimated number of unique values in the index. The cardinality statistics are calculated at various times, and can help the optimizer make improved decisions. |
Sub_part |
NULL if the entire column is included in the index, or the number of included characters if not. |
Packed |
NULL if the index is not packed, otherwise how the index is packed. |
Null |
NULL if NULL values are permitted in the column, an empty string if NULL 's are not permitted. |
Index_type |
The index type, which can be BTREE , FULLTEXT , HASH or RTREE . See Storage Engine Index Types. |
Comment |
Other information, such as whether the index is disabled. |
Index_comment |
Contents of the COMMENT attribute when the index was created. |
The WHERE
and LIKE
clauses can be given to select rows using more general conditions, as discussed in Extended SHOW.
CREATE TABLE IF NOT EXISTS `employees_example` ( `id` int(11) NOT NULL AUTO_INCREMENT, `first_name` varchar(30) NOT NULL, `last_name` varchar(40) NOT NULL, `position` varchar(25) NOT NULL, `home_address` varchar(50) NOT NULL, `home_phone` varchar(12) NOT NULL, `employee_code` varchar(25) NOT NULL, PRIMARY KEY (`id`), UNIQUE KEY `employee_code` (`employee_code`), KEY `first_name` (`first_name`,`last_name`) ) ENGINE=Aria; INSERT INTO `employees_example` (`first_name`, `last_name`, `position`, `home_address`, `home_phone`, `employee_code`) VALUES ('Mustapha', 'Mond', 'Chief Executive Officer', '692 Promiscuous Plaza', '326-555-3492', 'MM1'), ('Henry', 'Foster', 'Store Manager', '314 Savage Circle', '326-555-3847', 'HF1'), ('Bernard', 'Marx', 'Cashier', '1240 Ambient Avenue', '326-555-8456', 'BM1'), ('Lenina', 'Crowne', 'Cashier', '281 Bumblepuppy Boulevard', '328-555-2349', 'LC1'), ('Fanny', 'Crowne', 'Restocker', '1023 Bokanovsky Lane', '326-555-6329', 'FC1'), ('Helmholtz', 'Watson', 'Janitor', '944 Soma Court', '329-555-2478', 'HW1'); SHOW INDEXES FROM employees_example; +-------------------+------------+---------------+--------------+---------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | +-------------------+------------+---------------+--------------+---------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ | employees_example | 0 | PRIMARY | 1 | id | A | 7 | NULL | NULL | | BTREE | | | | employees_example | 0 | employee_code | 1 | employee_code | A | 7 | NULL | NULL | | BTREE | | | | employees_example | 1 | first_name | 1 | first_name | A | NULL | NULL | NULL | | BTREE | | | | employees_example | 1 | first_name | 2 | last_name | A | NULL | NULL | NULL | | BTREE | | | +-------------------+------------+---------------+--------------+---------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
© 2019 MariaDB
Licensed under the Creative Commons Attribution 3.0 Unported License and the GNU Free Documentation License.
https://mariadb.com/kb/en/show-index/