Database Tuning Tutorial (10) "Essence Chapter" 4 ways to solve the index not being used when you like '%str '

Source: Internet
Author: User

Third, index


7.4 ways to solve like '%str ' without using fuzzy queries


Last said, as long as the fuzzy query fuzzy values in front of the string, the index will not be used, '%aaa ' and ' _aaa ' are not!

As follows


It should be said that this is the MySQL to the programmer to open a joke. What if my table has a large amount of data and I need to retrieve it using a fuzzy query like ' percent '??

Next, I will share with you four ways to solve this problem!


1) Select Primary key

As long as the Select field is just the primary key, then the index is used (only valid for the INNODB database)

Like the following

Select Idfrom emp where ename like '%haha% ' \g

The index is used


SELECT * from Empwhere ename like '%haha% ' \g

The index is not used



In addition to the primary key, other fields must be set to overwrite the index for the index to take effect and cannot be indexed individually

For example, the following will not use the index


You can use the method of the step query, first select the primary key and then use the primary key to find other fields. But it seems to be more troublesome! Don't be afraid! Next, we will talk about an optimal method--covering the index Method!


2) Overlay Index method

An overlay index is a special multi-column index that is called an overwrite index when a multicolumn index points to all the fields in a query statement .

Using the overwrite index will solve the problem!

Create an overlay index


Of course, if you want to select many fields or even select*, you can create a multicolumn index to point to all the fields (InnoDB can not point to the primary key)



Attention:

The author finds that when the overlay index points to a field that is varchar (380) and more than 380, the overwrite index is invalidated!

3) Full-Text indexing method

This method has a large limitation.

Full-text indexing, which is useful only for the MyISAM engine . Mainly for the document, text retrieval, such as articles or paragraphs,.

It generates a list of all the words that appear in one of the data columns of a data table

Words less than 3 characters are not included in the full-text index and can be modified by modifying the MY.CNF option

Ft_min_word_len=3

But!

Full-text indexes are not exactly equivalent to fuzzy queries

For example, the title field has such data ' ABCD20088CCAA ', using a fuzzy query select * from articles wheretitle like '%2008% ' can be found, while using full-text Search select * from articles where match (title) against (' 2008 ') is not retrieved because 2008 is not a word!

4) using the full-text Search engine Toolkit

A segment string can be retrieved using a dedicated full-text Search open Source tool, such as Lucene, Sphinx, SOLR, and so on.



At the end of this chapter, the next chapter will return to the development source of the database-design a table.

Database Tuning Tutorial (10) "Essence Chapter" 4 ways to solve the index not being used when you like '%str '

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.