List index information for a table. View key names, column sequences, cardinality, and other index attributes.
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 thetbl_name FROM db_name syntax. These two statements are equivalent:
SHOW KEYS and SHOW INDEXES are synonyms for SHOW INDEX.
You can also list a table's indexes with the command:
The table stores similar information.
The following fields are returned by SHOW INDEX.
The WHERE and LIKE clauses can be given to select rows using more general conditions, as discussed in .
This page is licensed: GPLv2, originally from
SHOW {INDEX | INDEXES | KEYS}
FROM tbl_name [FROM db_name]
[WHERE expr]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 NULLs are not permitted.
Index_type
The index type, which can be BTREE, FULLTEXT, HASH or RTREE. See .
Comment
Other information, such as whether the index is disabled.
Index_comment
Contents of the COMMENT attribute when the index was created.
Ignored
Whether or not an index will be ignored by the optimizer. See . From .
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.
SHOW INDEX FROM mytable FROM mydb;
SHOW INDEX FROM mydb.mytable;mariadb-show -k db_name tbl_nameCREATE 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\G
*************************** 1. row ***************************
Table: employees_example
Non_unique: 0
Key_name: PRIMARY
Seq_in_index: 1
Column_name: id
Collation: A
Cardinality: 6
Sub_part: NULL
Packed: NULL
Null:
Index_type: BTREE
Comment:
Index_comment:
Ignored: NO
*************************** 2. row ***************************
Table: employees_example
Non_unique: 0
Key_name: employee_code
Seq_in_index: 1
Column_name: employee_code
Collation: A
Cardinality: 6
Sub_part: NULL
Packed: NULL
Null:
Index_type: BTREE
Comment:
Index_comment:
Ignored: NO
*************************** 3. row ***************************
Table: employees_example
Non_unique: 1
Key_name: first_name
Seq_in_index: 1
Column_name: first_name
Collation: A
Cardinality: NULL
Sub_part: NULL
Packed: NULL
Null:
Index_type: BTREE
Comment:
Index_comment:
Ignored: NO
*************************** 4. row ***************************
Table: employees_example
Non_unique: 1
Key_name: first_name
Seq_in_index: 2
Column_name: last_name
Collation: A
Cardinality: NULL
Sub_part: NULL
Packed: NULL
Null:
Index_type: BTREE
Comment:
Index_comment:
Ignored: NO