MySQL indexing and optimization like query

Source: Internet
Author: User

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

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.