View the index method of MySQL data table

The establishment of MySQL index is very important for the efficient operation of MySQL. The index can greatly improve the retrieval speed of MySQL. For example, if MySQL that is properly designed and uses indexes is a Lamborghini, then MySQL that is not designed and uses indexes is a human tricycle. So if we want to check which indexes are used in the MySQL data table at work, how should we check it?

How to check which indexes are used in the mysql data table.

mysql> show index from tblname;

Or:

mysql> show keys from tblname;

The following is the relevant description of the query results:

· Table The name of the table.

· Non_unique 0 if the index cannot include duplicate words. 1 if possible.

· Key_name The name of the index.

· Seq_in_index The column sequence number in the index, starting from 1.

· Column_name Column name.

· How Collation columns are stored in the index. In MySQL, there are values ​​'A' (ascending) or NULL (no sort).

· Cardinality An estimate of the number of unique values ​​in the index. It can be updated by running ANALYZE TABLE or myisamchk -a. 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 If the column is only partially indexed, the number of characters indexed. NULL if the entire column is indexed.

· Packed indicates how the keyword is packed. NULL if not compressed.

· Null If the column contains NULL, it contains YES. If not, the column contains NO.

· Index_type Index method used (BTREE, FULLTEXT, HASH, RTREE).

Supplement: MySQL indexes have many benefits, but excessive use of indexes will cause abuse. Therefore, the index will also have its shortcomings: although the index greatly improves the query speed, it will also reduce the speed of updating the table, such as INSERT, UPDATE and DELETE on the table. Because when updating the table, MySQL not only needs to save the data, but also save the index file. The above method of checking which indexes are used in the MySQL data table hopes to be helpful to you.

Related recommendations:

The difference between btree and hash indexes in MySQL

About redundant and duplicate indexes in mysql

Very important index operations in MySql

What are the disadvantages of mysql clustered index

The index is What? How to create an index in MySQL

The above is the detailed content of View the index method of MySQL data table. For more information, please follow other related articles on the PHP Chinese website!