Database optimization tutorial (10) [excellent chapter] solve the four ways in which indexes are not used when like '% str', tuning % str

Database optimization tutorial (10) [excellent chapter] solve the four ways in which indexes are not used when like '% str', tuning % str
Iii. Index

7. Four methods to solve like '% str' without fuzzy query

As mentioned above, the index will not be used as long as the fuzzy search value is prior to the string, neither '% aaa' nor' _ aaa' will be used!

As follows:

It should be said that this is a joke that Mysql has made to programmers. What should I do if my table has a large amount of data and needs to be searched using a fuzzy query like '% ??

Next, I will share with you how to solve this problem.Four Methods!

1) Select primary key

As long as the Select field is the primary key, the index will be used (only valid for the innodb database)

For example

[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

Then no index is used.

Except the primary key, other fields must be set to overwrite the index to make the index take effect.

For example, the following method does not use indexes.

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

2) covered Index Method

Covering indexes is a special multi-column index,When a multi-column index points to all the fields in a query statement, the multi-column index is called overwrite index..

Overwriting indexes can solve the problem!

Create overwriting Index

Of course, if you want to select many fields or even select *, you can create a multi-column index pointing to all fields (innodb can not point to the primary key)

Note:

I found that,If the fields to which the index points are overwritten are varchar (380) and above 380, the covered index will become invalid!

 

3) full-text indexing

This method has many limitations.

Full-text index,Useful only for the MyISAM Engine. Mainly for file and text retrieval, such as articles or paragraphs ,.

It generates a list of all the words in a data column of a data table.

Words with less than 3 characters are not included in the full-text index. You can modify the my. cnf option.

Ft_min_word_len = 3

But!

Full-text indexes are not exactly the same as fuzzy queries.

For example, if the title field has such data 'abc20088ccaa ', you can use the fuzzy query select * from articles wheretitle like' % 100' to find it, using full-text search select * from articles where match (title) against ('20160901'); cannot be retrieved because 2008 is not a word!

 

4) use the full-text search engine Toolkit

A specific string can be retrieved using lucene, sphenders, solr, and other open-source full-text retrieval tools.