How to query index status in mysql?

Mysql method of querying the index status: through the "SHOW INDEX" statement, the syntax format "SHOW INDEX FROM ccc43248daffbac9770dee47fdaff697 [ FROM e5c249aa15ff4c64720f99bfaecf8a60]"; through this statement, you can view the index Name, whether it is a unique index, the position of the field in the index, the column field name that defines the index, etc.

After the index creation is completed, you can use SQL statements to view the existing indexes. In MySQL, you can use the SHOW INDEX statement to view the indexes created on a table.

The syntax format for viewing the index is as follows:

SHOW INDEX FROM <表名> [ FROM <数据库名>]

The syntax description is as follows:

• ##722e3d59fd24604761db25f00f9b264f: Specify the data table to be viewed for the index name.

• 51ede4c6d43f3b2169203bd49746727d: Specify the database where the data table whose index needs to be viewed is located, which can be omitted. For example,

  SHOW INDEX FROM student FROM test;statement means to view the index of the student data table in the test database.

Example

SQL statements and running results are as follows.

mysql> SHOW INDEX FROM tb_stu_info2\G *************************** 1. row *************************** Table: tb_stu_info2 Non_unique: 0 Key_name: height Seq_in_index: 1 Column_name: height Collation: A Cardinality: 0 Sub_part: NULL Packed: NULL Null: YES Index_type: BTREE Comment: Index_comment: 1 row in set (0.03 sec)

The main parameters are described as follows:

Parameters Description Table represents the name of the data table to create the index, here is the tb_stu_info2 data table. Non_unique Indicates whether the index is a unique index. If it is not a unique index, the value of this column is 1; if it is a unique index, the value of this column is 0. Key_name represents the name of the index. Seq_in_index Indicates the position of the column in the index. If the index is a single column, the value of the column is 1; if the index is a combined index, the value of the column is 1. The column values are the order in which each column appears in the index definition. Column_name represents the column field that defines the index. Collation Indicates the order in which columns are stored in the index. In MySQL, ascending order displays the value "A" (ascending order), if displayed as NULL, it means no classification. Cardinality An estimate of the number of unique values in the index. Cardinality counts against statistics that are stored as integers, so even for small tables, the value does not need to be exact. The larger the cardinality, the greater the chance that MySQL will use the index when doing joins. Sub_part represents the number of indexed characters in the column. If the column is only partially indexed, the value of the column is the number of characters indexed; if the entire column is indexed, the value of the column is NULL. Packed Indicates how keywords are packed. If not compressed, the value is NULL. Null is used to display whether the index column contains NULL. If a column contains NULL, the value of the column is YES. If not, the value of this column is NO. Index_type Display the type and method used by the index (BTREE, FULLTEXT, HASH, RTREE). Comment Display comments. Recommended tutorial:

mysql video tutorial

The above is the detailed content of How to query index status in mysql?. For more information, please follow other related articles on the PHP Chinese website!