Why is your like statement not indexed?

Preface

This article aims to explain the most boring basic knowledge in the most popular language

This topic is quite interesting. Yesterday at noon, after lunch, a colleague suddenly asked, "Is there an index for like?" I said no, and another colleague retorted that yes, and some colleagues said it depends on the situation. I was a little confused and didn't know which statement was correct, so I decided to spend half an hour researching and verifying this problem, and finally got the answer.

How to verify it?

There is a rumor that there is a magic tool for MySQL performance optimization, called explain, which can analyze select statements and output detailed information about the select execution process, allowing developers to gain optimization ideas from this information.

Let's talk about the explain command provided by MySQL:

Syntax: explain SQL statement example:

1explain select * from user where id=1

After execution, its output has the following fields:

id
select_type
table
partitions
type
possible_keys
key
key_len
ref
rows
Extra

To know how to use explain naming, you must understand these fields.

1. id

The identifier of the SELECT query. Each SELECT statement is automatically assigned a unique identifier.

2. select_type

The type of each select query statement, the specific type and the corresponding function are as follows:

3. table

Displays which table the data in this row is checked from, but sometimes the short circuit does not display the actual table name.

4. partitions

Matching partitions (this is not very useful at present)

5. type

Access type, which indicates how MySQL finds the required row in the table. The corresponding values ​​and explanations are as follows:

Type is the access type. Its value represents the type used in the current query and is an important indicator of performance. As can be seen from the table, from top to bottom, the way to scan the table becomes wider and wider, and the performance becomes worse and worse. Therefore, for a query, it is best to keep it above the range level.

6. possible_keys

Actively indicate which index the query can use to find records in the table. That is, the fields in the query are listed with indexes, but they are not necessarily used by the query.

7. key

Displays the actual index/key used in the query, or NULL if there is no index. But if you want to force the query to use or ignore the index in the possible_keys column, you can use FORCE INDEX, USE INDEX, or IGNORE INDEX in the query.

8. key_len

Indicates the number of bytes used in the index.

9. ref

Indicates which columns or constants are used to look up values ​​in the index column.

10. rows

Displays the estimated number of rows required to find matching records for the current query.

11. Extra

Displays the solution used for the current query. It has the following situations:

Now that we have explained the syntax, let's do some actual operations. First, create a table:

-- Create table CREATE TABLE test(
id INT(11) NOT NULL AUTO_INCREMENT,
uname VARCHAR(255),
PRIMARY KEY(id) 
);

Then add an index to the uname field:

-- Add index ALTER TABLE test ADD INDEX uname_index (uname);

Check whether the index was added successfully:

-- Check whether there is an index SHOW INDEX FROM test;

The output is:

It can be seen that the index has been created successfully. Next, add some data:

-- Add some data INSERT INTO test VALUES(1,'jay');
INSERT INTO test VALUES(2,'ja');
INSERT INTO test VALUES(3,'bril');
INSERT INTO test VALUES(4,'aybar');

Everything is ready. Let's use the explain command to explore whether some like statements have indexes. There are four cases of like, namely no%, %%, left%, right%, and

1. Like field name

EXPLAIN SELECT * FROM test WHERE uname LIKE 'j';

The output is:

It can be seen that the value of type is range and the value of key is uname_index, which means that the index is used in this case.

2. like %field name%

EXPLAIN SELECT * FROM test WHERE uname LIKE '%j%';

The output is:

It can be seen that the value of type is ALL, which means a full table scan, and the value of key is NULL, which means that no index is used.

3. like % field name

EXPLAIN SELECT * FROM test WHERE uname LIKE '%j';

The output is:

It can be seen that the value of type is ALL, the value of key is NULL, and the index is also not used.

4. Like field name%

EXPLAIN SELECT * FROM test WHERE uname LIKE 'j%';

The output is:

It can be seen that the value of type is range and the value of key is uname_index, which means that the index is used in this case.

Summarize

From the above experiment, we can summarize the rule of whether like uses the index: for the like statement to make the index effective, the like cannot start with %, that is, statements such as (like %field name%) and (like %field name) will make the index invalid, while statements such as (like field name) and (like field name%) can use the index normally.

other

In order to verify the problem of like index, we studied MySQL's magical explain. However, explain can not only check the index usage, but also provide a lot of other performance optimization help. As for the specific usage, it is actually the same as mentioned above. List the explain results, and then follow the clues to check the related fields to get the corresponding content.

Well, the above is the full content of this article. I hope that the content of this article will have certain reference learning value for your study or work. If you have any questions, you can leave a message to communicate. Thank you for your support of 123WORDPRESS.COM.