Shulou(Shulou.com)05/31 Report--

This article mainly explains "how to use the index keyword of mysql". The content of the explanation is simple and clear, and it is easy to learn and understand. Please follow the editor's train of thought to study and learn "how to use the index keyword of mysql".

In mysql, the index keyword can be used to create an index, the syntax "CREATE INDEX index name ON table name (column name)", the syntax to view the index, the syntax "SHOW INDEX FROM table name", or to modify the index, the syntax "DROP INDEX index name ON table name".

The operating environment of this tutorial: windows7 system, mysql8 version, Dell G3 computer.

In mysql, index refers to the index, which is a special database structure, which is composed of one or more columns in the data table, and can be used to quickly query the records with a specific value in the data table.

The index keyword can be used to create an index, view an index, or modify an index.

Index keyword to create an index

You can create an index on an existing table using a CREATE INDEX statement specifically designed to create an index, but this statement cannot create a primary key.

CREATE INDEX index name ON table name (column name [length] [ASC | DESC])

Specifies the index name Multiple indexes can be created in a table, but each index has a unique name in the table.

Specifies the name of the table to be indexed

Specifies the name of the column to create the index You can usually consider the columns that often appear in the JOIN clause and WHERE clause in the query statement as index columns.

Optional Specifies that the index is created using the length characters before the column. Creating an index using part of a column helps to reduce the size of the index file and save the space occupied by the index column. In some cases, only column prefixes can be indexed. The length of an index column has a maximum limit of 255bytes (the maximum limit for MyISAM and InnoDB tables is 1000 bytes). If the length of an index column exceeds this limit, it can only be indexed with the prefix of the column. In addition, columns of type BLOB or TEXT must also be indexed with a prefix.

ASC | DESC: optional. ASC specifies that the indexes are sorted in ascending order, and DESC specifies that the indexes are arranged in descending order. The default is ASC.

For example, to add a new index to column c4, use the following statement:

CREATE INDEX idx_c4 ON t (c4)

By default, MySQL creates an B-Tree index if no index type is specified.

Index keyword View Index

SHOW INDEX FROM table name [FROM database name]

The syntax is as follows:

Specifies the name of the data table to view the index

Specifies the database in which the data table to view the index is located, which can be omitted For example, the SHOW INDEX FROM student FROM test; statement means to view the index of the student data table in the test database.

Example:

Mysql > SHOW INDEX FROM tb_stu_info2\ gateway * 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:

The parameter description Table represents the name of the data table in which the index was created, in this case the tb_stu_info2 data table. Non_unique indicates whether the index is unique. If it is not a unique index, the value of the column is 1; if it is a unique index, the value of the column is 0. Key_name represents the name of the index. Seq_in_index represents the position of the column in the index, and if the index is single, the value of the column is 1; if the index is a composite index, the value of the column is the order of each column 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), and if shown as NULL, there is no classification. An estimate of the number of unique values in the Cardinality index. The cardinality is counted according to the statistics stored as integers, so even for small tables, the value does not have to be accurate. The higher the cardinality, the greater the chance that MySQL will use the index when federating. Sub_part represents the number of characters indexed 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 compressed. If not compressed, the value is NULL. Null is used to show whether the index column contains NULL. If the column contains NULL, the value of the column is YES. If not, the value of the column is NO. Index_type displays the types and methods used by the index (BTREE, FULLTEXT, HASH, RTREE). Comment displays comments.

Index keyword modifies the index

DROP INDEX ON

The syntax is as follows:

The name of the index to delete

Specifies the name of the table in which the index is located

Description:

In MySQL, you can modify the index by deleting the original index and then creating an index with the same name as needed.

Thank you for your reading, the above is the content of "how to use the index keyword of mysql". After the study of this article, I believe you have a deeper understanding of how to use the index keyword of mysql. Here is, the editor will push for you more related knowledge points of the article, welcome to follow!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.