How to create multiple joint indexes in mysql

Mysql method to create multiple joint indexes: You can use the CREATE INDEX or ALTER TABLE statement to create them. When creating a joint index, the order of the columns should be carefully considered.

Index rules in MySQL:

(Recommended tutorial: mysql video tutorial)

1. Fields that need to be indexed must be in the where condition
2. Fields with a small amount of data do not need to be indexed
3. If the where condition is an OR relationship, indexing will not work
4. Comply with the leftmost principle

What is a joint index?

Union index is also called compound index. For composite indexes, 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). It can support 3 combinations of a | a,b| a,b,c for search, but does not support b,c for search. When the leftmost field is a constant reference , the index is very effective.

An index on two or more columns is called a composite index

With additional columns in the index, you can narrow the scope of your search, but use a An index with two columns is different from using two separate indexes. The structure of a composite index is similar to a phone book, where a person's name is composed of a first and last name. The phone book is first sorted by last name pairs, and then sorted by first name for people with the same last name. A phone book is very useful if you know your last name, more useful if you know both your first and last name, but useless if you only know your first name but not your last name.

So when creating a composite index, you should carefully consider the order of columns. Composite indexes are useful when searching on all columns in the index or only on the first few columns; they are not useful when searching on any subsequent columns.

Index creation:

You can create an index when executing the CREATE TABLE statement, or you can use CREATE INDEX or ALTER TABLE alone to add an index to the table.

Case DEMO:

ALTER TABLE table_name ADD INDEX index_name (column_list)
ALTER TABLE table_name ADD UNIQUE (column_list)
ALTER TABLE table_name ADD PRIMARY KEY (column_list)
CREATE INDEX index_name ON table_name (column_list)
CREATE UNIQUE INDEX index_name ON table_name (column_list)

The above is the detailed content of How to create multiple joint indexes in mysql. For more information, please follow other related articles on the PHP Chinese website!