SHOW INDEX
You are viewing an old version of this article. View
the current version here.
Syntax
SHOW {INDEX | INDEXES | KEYS} FROM tbl_name [FROM db_name] [WHERE expr]
Contents
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.
- 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
.
- The column's sequence in the index, beginning with
- Column_name
- Column name.
- Collation
- Either
A
, if the column is sorted in ascending order in the index, orNULL
if it's not sorted.
- Either
- 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
orRTREE
. See Storage Engine Index Types.
- The index type, which can be
- 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 | | | +-------------------+------------+---------------+--------------+---------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
Comments
Comments loading...
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.