MySQL Fuzzy query optimization

Use the following function to make a fuzzy query, if the occurrence of the position >0, indicates that the string is contained.

Query efficiency is higher than like.
Such as:

Table.field like  '%aaa% ' can be changed to locate (' AAA ', Table.field) > 0

Note: Locate (SUBSTR,STR)

Use explain to see the results, the less rows the better!

1, use like query

2, query with locate

It is obvious that, although the rows of the two are the same here, the value of filtered, after locate, is 10 times times the value of like.

Note: the column is filtered described in the MySQL manual:

The filtered column indicates an estimated percentage of table rows that would be filtered by the table condition. That's, rows shows the estimated number of rows examined and rowsxfiltered/100 shows the number of rows that would be Joined with previous tables. This column is displayed if EXPLAIN EXTENDED. (New in MySQL 5.1.12)

In other words,the higher the filtered, the better!

MySQL Fuzzy query optimization