MySQL Index classification

Index fields in database tables can greatly improve the query speed. By making good use of these indexes, you can make MySQL query and operation more efficient. Index is the key to quick search. The establishment of MySQL indexes is very important for the efficient operation of MySQL. The following describes several common MySQL index types.

1. Common Indexes

This is the most basic index type, and it has no limitations such as uniqueness.Common indexes can be created in the following ways:
(1) Create an index, for example, create Index name on tablename (column name 1, column name 2 ,...);
(2) modify a table, for example, alter table tablename add Index name (column name 1, column name 2 ,...);
(3) specify an index when creating a table, such as create table tablename ([...], index name (column name 1, column name)

2 ,...));

2. Unique Index

This index is basically the same as the previous "normal index", but there is a difference:All values of an index column can only appear once, that is, they must be unique.You can create a unique index in the following ways:
(1) Create an index, for example, create unique index name on tablename (column list );
(2) modify a table, for example, alter table tablename add unique index name (column list );
(3) specify an index when creating a table, for example, create table tablename ([...], unique index name (column Column

Table ));

3. Primary Key

A primary key is a unique index, but it must be specified as a "primary key ". If you have used auto _For increment columns, you may already be familiar with the concept of primary keys.The primary key is generally specified during table creation, for example, "create table tablename ([...], primary key (column list ));". However, we can also add a primary key by modifying the table, for example,"Alter table tablename add primary key (column list );". Each table can have only one primary key. (The primary key is equivalent to an aggregate index, which is the fastest search index)

4. Single-Column and multi-column Indexes

An index can be a single-column index or multiple-column index.

(1) A single column index is a common index of a column field.

(2) A multi-column index is an index containing multiple column fields.

Alter table student add index Sy (name, age, score );

Index Sy is a multi-column index. Multi-column indexes are valid only in the following situations:

Select * from student where name = 'jia 'and age> = '12' // The first column field and

Second Field

Select * from student where name = 'jia '// The where condition contains only the first column of fields

Select * from student where name = 'jia 'and score <60 // The where condition contains the first column field and the third word.

Segment

Conclusion: Multi-column indexes are valid only when the where condition contains the first column field in the index.

5. Select an index Column

How to select an index column depends on the query conditions first. Generally, the columns in the query conditions are used as indexes.