Syntax
------ 
SHOW {INDEX | INDEXES | KEYS} 
 FROM tbl_name [FROM db_name]
 [WHERE expr]
 
Description
----------- 
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.
 
Examples
-------- 
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 | | |
+-------------------+------------+---------------+--------------+---------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
 

 
 
 
 
 
 
 

URL: https://mariadb.com/kb/en/show-index/