Why does your like statement have no index?

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

This is an interesting topic. Yesterday after lunch suddenly a colleague jumped out a sentence: “Does like have an index?” “, I said no, another colleague refuted, “Yes, it depends on the situation, some colleagues said, which was a bit confused, did not know that the statement is correct, so I decided to spend half an hour to research and verify the question, finally got the answer.

How do you verify that?

Rumor has it that MySQL performance tuning has a magic tool called Explain that can analyze select statements and output detailed information about the select execution process, allowing developers to derive optimization ideas from this information.

Here is the explain command provided by MySQL:

Syntax: explain SQL statements

1explain select * from user where id=1

Copy the code

After execution, its output has the following fields:

• id
• select_type
• table
• partitions
• type
• possible_keys
• key
• key_len
• ref
• rows
• Extra

In order to understand how to use explain names, you have to understand these fields

1. id

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

2. select_type

The types of each select query term are as follows:

Type name	explain
SIMPLE	Simple SELECT, no UNION or subquery, etc
PRIMARY	If the query contains any complex subparts, the outermost select is marked as PRIMARY
UNION	The second or subsequent SELECT statement in UNION
DEPENDENT UNION	The second or subsequent SELECT statement in the UNION, depending on the query outside
UNION RESULT	The result of the UNION
SUBQUERY	The first SELECT in the subquery
DEPENDENT SUBQUERY	The first SELECT in the subquery depends on the external query
DERIVED	A subquery of the SELECT FROM clause of a derived table
UNCACHEABLE SUBQUERY	The results of a subquery cannot be cached, and the first line of the external link must be reevaluated

3. table

The row is used to query the table, but sometimes the table name is not the real one.

4. partitions

Matching partitions (this is not very useful right now)

5. type

Access type, which represents how MySQL finds the desired rows in the table. The corresponding values and interpretations are as follows:

Type name	Optimal level	explain
system	1	Table has only one row
const	2	The table has at most one matching row and is read at the start of the query
eq_ref	3	Use a primary key or unique key as a condition for multiple table joins, and read only one row from the table
ref	4	The index that serves as the query condition is read from the table in rows that match the index value in each table
fulltext	5	Full-text index search
ref_or_null	6	Same as ref, but added NULL value query support
index_merge	7	Indicates that the index merge optimization method is used
unique_subquery	8	The replacement in subquery was used
index_subquery	9	The replacement in subquery is used, but only for non-unique indexes in the subquery
range	10	Only rows of a given range are retrieved, using an index to select rows
index	11	A full table scan, but the table is scanned in index order
ALL	12	Full table scan to find matching rows

As an access type, type represents the type used in the current query and is an important indicator of performance. As can be seen from the table, the performance deteriorates as the table scanning methods get wider from top to bottom. Therefore, for a query, it is best to keep the range level above.

6. possible_keys

Actively indicate which indexes the query can use to find records in the table, i.e. fields that are indexed but not necessarily used by the query that are listed in the query.

7. key

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

8. key_len

Represents the number of bytes used in the index.

9. ref

Indicates which columns or constants are used to find values on index columns.

10. rows

Displays the number of record rows estimated by the current query to find a matching record.

11. Extra

Displays the solution used for the current query in the following cases:

Type name	explain
Using where	Column data is returned from a table that only uses the information in the index without reading the actual action,
Using temporary	Indicates that MySQL needs to use temporary tables to store result sets, common in sort and group queries
Using filesort	A sort operation in MySQL that cannot be done using an index is called “file sort”.
Using join buffer	The value change highlights the lack of use of indexes when retrieving connection conditions and the need for a connection buffer to store intermediate results. If this value is present, it should be noted that indexes may be added to improve performance depending on the query.
Impossible where	This value emphasizes that the WHERE statement will result in no qualifying rows.
Select tables optimized away	This value means that by using the index alone, the optimizer may return only one row from the aggregate function result

---

Now that we’re done with the syntax, let’s start by creating a table:

1- create a table

2CREATE TABLE test(

3id INT(11) NOT NULL AUTO_INCREMENT,

4uname VARCHAR(255),

5PRIMARY KEY(id) 

6);

Copy the code

Then index the uname field:

1Add index to index

2ALTER TABLE test ADD INDEX uname_index (uname);

Copy the code

Check whether the index is added successfully:

1-- Check if there is an index

2SHOW INDEX FROM test;

Copy the code

The output is:

Now that the index has been created, add some data:

1Add some data

2INSERT INTO test VALUES(1.'jay');

3INSERT INTO test VALUES(2.'ja');

4INSERT INTO test VALUES(3.'bril');

5INSERT INTO test VALUES(4.'aybar');

Copy the code

When all is ready, let’s use the explain command to explore whether some like statements have indexes.

1. Name of the like field

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

Copy the code

The output is:

You can see that the value of type is: range and the value of key is uname_index, which means that in this case, the index is used.

2. Like % field name %

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

Copy the code

The output is:

Type = ALL; key = NULL; no index is used.

3. Name of the like % field

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

Copy the code

The output is:

As can be seen:



The value of type is ALL, the value of key is NULL, and there is no index.

4. Like field name %

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

Copy the code

The output is:

As can be seen:



The value of type is: range, and the value of key is uname_index, which means that in this case, an index is used.

conclusion

From the above experiment, we can summarize the rule of whether like uses index: (like %), (like %), (like %), (like %), (like %), (like %), (like %), (like %), (like %), (like %), (like %), (like %), (like %).

other

In order to verify the problem of like index, we studied the MySQL magic Explain, but explain can not only check index usage, but also provide many other performance optimization help. As for the specific usage, it is the same as above. Then follow through and look up the relevant fields to get the corresponding content.

---

Find this article helpful? Please share with more people to pay attention to “programming without boundaries”, improve the installation force skills