Implementation of MySQL joint index (composite index)

Joint Index

The definition of the joint index in this article is (MySQL):

ALTER TABLE `table_name` ADD INDEX (`col1`,`col2`,`col3`);

Advantages of joint indexes

If there is more than one SQL statement, two conditions are required.

SELECT * FROM `user_info` WHERE username='XX',password='XXXXXX';

When the index is searching the password field, the amount of data is greatly reduced, the index hit rate is reduced, and the efficiency of the index is increased.

The size of a matching index is smaller than that of a single index, and it is just an index tree, which saves more time and space complexity than a single column index.

The essence of joint index hits (understanding of the leftmost match)

definition

When creating a ( col1 , col2 , col3 ) joint index, it is equivalent to creating a ( col ) single-column index. For the ( clo1 , clo2 ) joint index and the ( col1 , col2 , col3 ) joint index to take effect, you can only use the three combinations of col1 and col1 , col2 and col1 , col2 , col3 ; of course, the combination of col1 and col3 is also possible, but in fact only the index of col1 is used, col3 is not used!

Diagram

Popular understanding

A joint index is like a phone book organized by姓氏——名字. You can only hit the index if you first determine the last name. The following statements can correctly hit the joint index (the fields directly in = and IN can be in random order, and the MySQL query optimizer can optimize it into an index-recognized form)

-- Only hit col1, col2
SELECT * FROM `table_name` WHERE `col1`='XX';

-- hit col1, col2. The order of col1 and col2 can be reversed SELECT * FROM `table_name` WHERE `clo1`='XX',`clo2`='XXX'; 
SELECT * FROM `table_name` WHERE `clo2`='XXX', `clo1`='XX';

-- hit col1,col2,col3. Similarly, the order of the three columns can be reversed. SELECT * FROM `table_name` WHERE `col1`='X',`col2`='XX',`col3`='XXX';
SELECT * FROM `table_name` WHERE `col1`='X',`col3`='XX',`col2`='XXX';
SELECT * FROM `table_name` WHERE `col2`='X',`col3`='XX',`col1`='XXX';

This is the end of this article about the implementation of MySQL joint index (compound index). For more relevant MySQL joint index content, please search for previous articles on 123WORDPRESS.COM or continue to browse the following related articles. I hope everyone will support 123WORDPRESS.COM in the future!