Mysql fuzzy query optimization and mysql fuzzy query

Mysql fuzzy query optimization and mysql fuzzy query

First, create an index for the fields to be searched in the database (mysql index is unfamiliar, please refer to here ).

Secondly, use the following function for fuzzy search. If the position is greater than 0, it indicates that the string is included.

Query efficiency is higher than like.
For example:

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

Note: locate (substr, str)

Use explain to view the results. The fewer rows, the better!

1. query with like

2. query with locate

Obviously, although the two rows are the same here, the value of filtered is 10 times the value of like after locate is used.

Note:filteredColumn is described in the MySQL manual:

The filtered column indicates an estimated percentage of table rows that will be filtered by the table condition. that is, rows shows the estimated number of rows examined and rows × filtered/100 shows the number of rows that will be joined with previous tables. this column is displayed if you use explain extended. (New in MySQL 5.1.12)

That is to say,The higher the filtered, the better.!