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

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

[Plain]View Plaincopy

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

The index is used

[Plain]View Plaincopy

1. 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.

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