MySQL indexing and optimization like query

See: http://blog.yemou.net/article/query/info/tytfjhfascvhzxcyt101

1. The like%keyword index fails with a full table scan. But you can flip function +like before fuzzy query + build a rollover function index = Go Flip function index, do not go full table scan.

2. The like keyword% index is valid.

3. The like%keyword% index fails, and you cannot use a reverse index.

====================================================================

1. Use the following function to make a fuzzy query, if the location 〉0 appears, indicates that the string is contained.

Query efficiency is higher than like.

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

LOCATE (SUBSTR,STR)

POSITION (substr in str)

Returns the substring substr in the first occurrence of the string str, if SUBSTR is not inside STR, returns 0.

Using InStr

Select COUNT (*) from table T where InStr (T.column, ' xx ') > 0

This query works very well and is fast.

2. Querying the records of%XX

Select COUNT (c.c_ply_no) as Count

From Policy_data_all C, Item_data_all I

where c.c_ply_no = I.c_ply_no

and i.c_lcn_no like '%245′

At execution time, the execution plan shows that the consumption value, IO value, CPU value are very large, because the fuzzy query in front of like after the result of index invalidation, full table scan

Workaround: This only the former fuzzy SQL can be modified by the following wording

Select COUNT (c.c_ply_no) as Count

From Policy_data_all C, Item_data_all I

where c.c_ply_no = I.c_ply_no

and reverse (i.c_lcn_no) like reverse ('%245′)

Use the flip function +like before the fuzzy query + set up the rollover function index = Go Flip function index, do not go full scan. Effectively reduce consumption, IO value, CPU value of these three indicators, especially the reduction of IO value.

MySQL indexing and optimization like query