Does mysql have a joint index?

There is a joint index in mysql; the joint index refers to indexing two or more column fields on the table, also called a composite index. If only any column behind the joint index is executed When searching, the index will not have any effect. The syntax for creating the index is "create index index name on table name (field name 1, field name 2,...)".

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

Does mysql have a joint index?

Mysql has a joint index

Joint index: also called a composite index, refers to two or more indexes on the table Index more than one column field.

Mysql uses the fields in the index from left to right. A query can only use part of the index, but only the leftmost part. For example, the index is key index (a,b,c), which can support search in three combinations: a | a, b | a, b, c, but does not support search in combination of b and c.

Tip: Only for When searching on any column behind the joint index, the index will not have any effect

-- 用户表
CREATE TABLE `user` (
  `id` int(4) NOT NULL COMMENT '主键ID',
  `name` varchar(4) NOT NULL COMMENT '姓名',
  `age` int(3) NOT NULL COMMENT '年龄',
  PRIMARY KEY (`id`)
)

1. Create the index

As shown in the figure above, we have created it User table, if we want to create a joint index on the name and age columns of the table, we can use the following SQL:

create index index_name_age on user (name,age);

The syntax for creating a joint index: create index index name on table name (field name 1 ,Field name 2,...)

2. Delete the index

If we feel that the joint index created is not appropriate, we can use the following SQL to delete the joint index :

drop index index_name_age on user;

Or use: alter table table name drop index index name

alter table user drop index index_name_age;

3. An error will be reported when an index with the same name exists

Assume it has been created Combined index (index_name_age), if you create the index again, the following error will be reported:

Query : create index index_name_age on user (name,age)
Error Code : 1061
Duplicate key name 'index_name_age'

4. View the index

The syntax for viewing the index: show index from table Name

SHOW INDEX FROM USER;

Recommended learning: mysql video tutorial

The above is the detailed content of Does mysql have a joint index?. For more information, please follow other related articles on the PHP Chinese website!