Shulou(Shulou.com)05/31 Report--

This article will explain in detail how to solve the problem of like'% str' in database tuning when the index is not used, and the content of the article is of high quality, so the editor will share it for you to do a reference. I hope you will have a certain understanding of the relevant knowledge after reading this article.

Indexes

1. Four methods to solve the problem that like'%str' does not use fuzzy query

At the end of the last lecture, as long as the fuzzy value of the fuzzy query precedes the string, the index will not be used, neither'% aaa' 'nor' _ aaa'!

As follows

It should be said that this is a joke played by Mysql on the programmers. What if my table has a large amount of data and needs to be retrieved by a fuzzy query such as like'%%'?

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

1) Select primary key

As long as the field of Select happens to be the primary key, then the index is used (valid only for innodb databases)

Like the one below.

Select idfrom emp where ename like'% %'\ G

The index is used.

Select * from empwhere ename like'% %'\ G

Do not use the index

Except for the primary key, other fields must be set to override the index for the index to take effect, and the index cannot be set separately

For example, the following one will not use an index.

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

2) covering index method

An overlay index is a special multi-column index, which is called an overlay index when it points to all the fields in a query statement.

Using an overlay index can solve the problem!

Create an overlay index

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

Note:

The author finds that when the fields pointed to by the overlay index are varchar (380) and above, the overlay index will be invalid!

3) full-text indexing

This method has great limitations.

Full-text indexing is useful only for the MyISAM engine. Mainly aimed at the retrieval of documents and texts, such as articles or paragraphs.

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

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

Ft_min_word_len=3

But!

Full-text index is not exactly the same as fuzzy query.

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

4) use the full-text search engine toolkit

A certain string can be retrieved by using lucene, Sphinx, solr and other special open source full-text search tools.

On how to solve like'% str' in database tuning when the index is not used to share here, I hope the above content can be of some help to you, can learn more knowledge. If you think the article is good, you can share it for more people to see.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.