W3cubDocs

/MariaDB

SHOW INDEX

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      |         |               |
+-------------------+------------+---------------+--------------+---------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

Content reproduced on this site is the property of its respective owners, and this content is not reviewed in advance by MariaDB. The views, information and opinions expressed by this content do not necessarily represent those of MariaDB or any other party.

© 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/