Detailed explanation of MySQL combined index and leftmost matching principle

Detailed explanation of MySQL combined index and leftmost matching principle

Preface

I have seen many articles about the leftmost prefix matching of MySQL joint index on the Internet before, and I thought I understood its principle. Recently, when I communicated with the interviewer during the interview, I found that I had missed some things. Here I will organize the content in this regard.

When to create a composite index?

When our where query has multiple condition queries, we need to create a composite index for the query columns

Why not create an index on every column?

  • Reduce overhead
  • Covering Index
  • High efficiency

Reduce overhead: If you create a composite index for col1, col2, and col3, it is equivalent to creating three indexes: (col1), (col1, col2), and (col1, col2, col3). Covering index: If you query SELECT col1, col2, col3 FROM table name, since the queried fields exist in the index page, you can get them directly from the index without going back to the table for query.

High efficiency: Create indexes for col1, col2, and col3 respectively. MySQL will only select the column with the highest recognition as the index. Assume there are 1 million data, and an index filters out 10% of the data, then 10 million data can be filtered out; for a combined index, 1 million*10%*10%*10%=1000 data can be filtered out.

Leftmost matching principle

Suppose we create a composite index of (col1, col2, col3), which is equivalent to sorting the col1 column. That is, we create a composite index based on the leftmost column. As long as the query condition contains the leftmost column, the query will use the index.

Create a test table

CREATE TABLE `student` (
 `id` int(11) NOT NULL,
 `name` varchar(10) NOT NULL,
 `age` int(11) NOT NULL,
 PRIMARY KEY (`id`),
 KEY `idx_id_name_age` (`id`,`name`,`age`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

Fill 1 million test data

DROP PROCEDURE pro10;
CREATE PROCEDURE pro10()
BEGIN
	DECLARE i INT;
	DECLARE char_str varchar(100) DEFAULT 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789';
	DECLARE return_str varchar(255) DEFAULT '';
	DECLARE age INT;
	SET i = 1;
	WHILE i < 5000000 do
		SET return_str = substring(char_str, FLOOR(1 + RAND()*62), 8);
		SET i = i+1;
		SET age = FLOOR(RAND() * 100);
		INSERT INTO student(id, name, age) values(i, return_str, age);
	END WHILE;
END;

CALL pro10();

Scenario Testing

EXPLAIN SELECT * FROM student WHERE id = 2;

You can see that the query uses the index

EXPLAIN SELECT * FROM student WHERE id = 2 AND name = 'defghijk';

You can see that the query uses the index

EXPLAIN SELECT * FROM student WHERE id = 2 AND name = 'defghijk' and age = 8;

You can see that the query uses the index

EXPLAIN SELECT * FROM student WHERE id = 2 AND age = 8;

You can see that the query uses the index

EXPLAIN SELECT * FROM student WHERE name = 'defghijk' AND age = 8;

It can be seen that the query does not use the index, the type is index, the number of query rows is 4989449, and almost the entire table is scanned. Since the combined index only sorts the leftmost column, only the name and age can be fully scanned.

EXPLAIN SELECT * FROM student WHERE name = 'defghijk' AND id = 2;

EXPLAIN SELECT * FROM student WHERE age = 8 AND id = 2;

EXPLAIN SELECT * FROM student WHERE name = 'defghijk' and age = 8 AND id = 2;

You can see that the above query also uses the index. The results are the same when the id is placed in the front or the back. MySQL will find the most efficient query method, which is to query based on the id first.

Summarize

As shown in the above test, as long as the columns in the query condition contain the leftmost column of the composite index, the index will be used for the query regardless of the position of the column in the query condition.

Well, that’s all for this article. I hope the content of this article will be of certain reference value to your study or work. Thank you for your support of 123WORDPRESS.COM.

You may also be interested in:
  • In-depth analysis of the leftmost matching principle of MySQL joint index
  • A brief discussion on MySQL index design principles and the differences between common indexes
  • A brief understanding of the three principles of adding MySQL indexes
  • MySQL index leftmost principle example code
  • Understand MySQL index creation principles in one article

<<:  VMware configuration hadoop to achieve pseudo-distributed graphic tutorial

>>:  How to use Vuex's auxiliary functions

Recommend

Problems and solutions when replacing Oracle with MySQL

Table of contents Migration Tools Application tra...

How to create a trigger in MySQL

This article example shares the specific code for...

CSS solves the misalignment problem of inline-block

No more nonsense, post code HTML part <div cla...

How to hide a certain text in HTML?

Text hiding code, hide a certain text in HTML Copy...

Introduction to fourteen cases of SQL database

Data Sheet /* Navicat SQLite Data Transfer Source...

Specific method of viewing user authorization information in mysql

Specific method: 1. Open Command Prompt 2. Enter ...

Detailed explanation of MySQL remote connection permission

1. Log in to MySQL database mysql -u root -p View...

Vue ElementUI Form form validation

Form validation is one of the most commonly used ...

Example code comparing different syntax formats of vue3

The default template method is similar to vue2, u...

How to install MySQL Community Server 5.6.39

This article records the detailed tutorial of MyS...

JavaScript implements select all and unselect all operations

This article shares the specific code for JavaScr...

Detailed explanation of how to use Node.js to implement hot reload page

Preface Not long ago, I combined browser-sync+gul...

Design theory: people-oriented design concept

<br />When thoughts were divided into East a...