How to optimize the slow Like fuzzy query in MySQL

1. Introduction:

I built a "Student Management System" in which there is a student table and four tables (group table, class table, tag table, city table) for joint fuzzy query, the efficiency is very low, so I thought about how to improve the efficiency of like fuzzy query

Note: Before reading this blog, please check: How to view the execution time of SQL statements in MySQL

2. The first idea is to build an index

1. The like %keyword index is invalid, so a full table scan is used.

2. Like keyword% index is valid.

3. Like %keyword% index is invalid, use full table scan.

Tested using explain:

Original table (Note: the case uses the student table as an example)

-- User table create table t_users(
                        id int primary key auto_increment,
-- Username varchar(20),
-- Password password varchar(20),
-- Real name real_name varchar(50),
-- Gender 1 means male 0 means female sex int,
--birth date,
-- Mobile phone number mobile varchar(11),
-- The uploaded avatar path head_pic varchar(200)
);

Create an index

#create index index name on table name (column name);                 
create index username on t_users(username);

Like %keyword% index is invalid, use full table scan

explain select id,username,password,real_name,sex,birth,mobile,head_pic 
 from t_users where username like '%h%';

like keyword% index is valid.

 explain select id,username,password,real_name,sex,birth,mobile,head_pic 
 from t_users where username like 'wh%';

Like %keyword index is invalid, and full table scan is used.

3. INSTR

I had never heard of this at first, but after looking up some information today, I learned about this precious thing.

instr(str,substr) : Returns the position of the first occurrence of substr in string str. If the string is not found, it returns 0, otherwise it returns the position (starting from 1)

#instr(str,substr) method select id,username,password,real_name,sex,birth,mobile,head_pic 
      from t_users  
      where instr(username,'wh')>0 #0.00081900
#Fuzzy query select id,username,password,real_name,sex,birth,mobile,head_pic 
        from t_users 
        where username like 'whj'; # 0.00094650

The main reason why the efficiency difference between the two is not big is that the data is small. It is best to prepare more original data for testing for the best effect.

P.S. Does Like use index?

1. The like %keyword index is invalid, so a full table scan is used. But you can use flip function + fuzzy query before like + create flip function index = use flip function index instead of full table scan.

2. Like keyword% index is valid.

3. The like %keyword% index is invalid and the reverse index cannot be used.

Summarize

This is the end of this article about how to optimize the slow Like fuzzy query in MySQL. For more information about optimizing the slow Like fuzzy query in MySQL, please search for previous articles on 123WORDPRESS.COM or continue to browse the related articles below. I hope you will support 123WORDPRESS.COM in the future!