Shulou(Shulou.com)06/02 Report--

This article mainly introduces the MySQL Like fuzzy query speed is too slow how to optimize, the article is very detailed, has a certain reference value, interested friends must read it!

The first idea is to build an index.

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

2. The like keyword% index is valid.

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

Tested it with explain:

Original table (Note: the case is exemplified by the student table)

-- user table create table t_users (id int primary key auto_increment,-- username username varchar (20),-- password password varchar (20),-- real name real_name varchar (50) -- Sex 1 indicates male 0 indicates female sex int,-- date of birth, birth date,-- mobile phone number mobile varchar (11),-- uploaded avatar path head_pic varchar (200)

Build an index

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

Like keyword% index invalidated, using full table scan

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

The like keyword% index is valid.

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

The like keyword index is invalid, using a full table scan.

INSTR

I never heard of this at first. After consulting the materials today, I knew that there was this precious thing.

Instr (str,substr): returns the position of the first occurrence of the substr substring in the string str. If no string is found, 0 is returned, otherwise the position is returned (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.0008190" 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 for the small efficiency gap between the two is that there is less data, and it is best to prepare more raw data for testing.

The above is all the contents of the article "how to optimize the Like fuzzy query speed in MySQL is too slow". Thank you for reading! Hope to share the content to help you, more related knowledge, welcome to follow the industry information channel!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.