How to solve the slow speed of MySQL Like fuzzy query

Question: Although the index has been created, why is the Like fuzzy query still so slow?

Like whether to 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.

A simple test using mysql explain is as follows:

explain select * from company_info where cname like '%小%'

explain select * from company_info where cname like '小%'

Oracle like '%...%' optimization

1. Try not to use like '%%'

2. For like '%' (not starting with %), Oracle can apply the index on column

3. For like '%…' (not ending with %), you can use reverse + function index to change it to like '%'

4. If you have to use like'%%', use the Oracle internal function: INSTR() to solve it.

select * from emp2 where job like '%RE%' and ename like '%A%' and mgr like '%3%' ; --Scan the entire table, which is slow select * from emp where instr(job,'RE')>0 and instr(ename,'A')>0 and instr(mgr,'3')>0 ; --Search only for fields, which is fast

INSTR in MySQL (not the same as in Oracle)

INSTR(str,substr)

Returns the position of the first occurrence of substr in string str. This is identical to the two-argument form of LOCATE(), except that the order of the arguments is reversed.

INSTR(field name, string)

This function returns the position of a string in the content of a field. If the string is not found, it returns 0, otherwise it returns the position (starting from 1)

SELECT * FROM tblTopic ORDER BY INSTR( topicTitle, 'ha' ) > 0 DESC
SELECT INSTR( topicTitle, 'ha' ) FROM tblTopic

Later, I tried to split the SQL statement for querying a table into several SQL statements, run them simultaneously on the server, and finally merge the results. Unfortunately, my skills are too shallow, so the implementation is not that good.

I also tried to create a full-text index, but the database didn't allow me to do so.

Finally, the demand side compromised and adopted the index in the format of Like keyword%

If anyone can give me some advice on the solution to the fuzzy Like query, I would be very grateful!

The above is the full content of this article. I hope it will be helpful for everyone’s study. I also hope that everyone will support 123WORDPRESS.COM.