Practical application of MySQL show index syntax

The following articles mainly describe the actual usage of the MySQL show index syntax and the specific content of the actually viewed INDEX status (syntax, if you are curious, the following articles will unveil its mysteries.

 
 

  
  
SHOW INDEX FROM tbl_name [FROM db_name] 
 
 

MySQL show index returns the table INDEX information. The format is similar to that of SQLStatistics in ODBC.

MySQL show index returns the following fields:

Table

Table name.

Non_unique

If the index cannot contain duplicate words, the value is 0. If yes, it is 1.

Key_name

The name of the index.

Seq_in_index

The column serial number in the index, starting from 1.

Column_name

Column name.

Collation

How the column is stored in the index. In MySQLSHOW INDEX syntax, values 'a' are in ascending order) or NULL is not classified ).

Cardinality

The estimated number of unique values in the index. You can update analyze table or myisamchk-a by running analyze table. The base number is counted based on the statistical data stored as an integer. Therefore, this value is not required to be accurate even for small tables. The larger the base, the larger the chance for MySQL to use the index for union.

Sub_part

If a column is partially indexed, it is the number of indexed characters. If the entire column is indexed, the value is NULL.

Packed

Indicates how keywords are compressed. If it is not compressed, It is NULL.

Null

If the column contains NULL, YES is included. If NO, the column contains NO.

Index_type

Used Index Methods: BTREE, FULLTEXT, HASH, RTREE ).

Comment

Multiple comments.

You can use db_name.tbl_name as another form of tbl_name FROM db_name syntax. These two statements are equivalent:

 
 

  
  
mysql> SHOW INDEX FROM mytable FROM mydb;  
  
  
mysql> SHOW INDEX FROM mydb.mytable;  
 
 

Show keys is a synonym for MySQL show index. You can also use the mysqlshow-k db_name tbl_name command to list the indexes of a table.

Show innodb status syntax

 
 

  
  
SHOW INNODB STATUS 
 
 

In MySQL 5.1, This is a synonym for show engine innodb status, but it is not supported.