Performance of query LIKE in Mysql

Asked

Viewed 654 times

8

Is there any way to increase the performance of a query with LIKE '%string%' in Mysql?

I know that if the LIKE for 'string%', is faster. The problem is when the % is at the beginning of the string. There is some kind of index that we can create to have more performance in this query?

mysql query operators índices operator-like
  • 4

    vc could use http://dev.mysql.com/doc/refman/5.0/en/fulltext-search.html if your strings are large enough. I imagine that internally it builds indexes taking into account all the words of the text and maybe it gets faster

  • A relevant article on the scalability of LIKE and FULLTEXT: http://makandracards.com/makandra/12813-performance-analysis-of-mysql-s-fulltext-indexes-and-like-queries-for-full-text-search

  • 2

    You could migrate this type of search to a search engine, maybe Solr (http://lucene.apache.org/solr/)

  • 2

    If you really want to "stretch the bow"... do a reverse dictionary of all the words in each record. This requires an extra coding effort and quite a structure. However, it is one of the techniques that search engines use to be so performative.

2 answers

2

Your answer is no. As commented, if the operator is not used MATCH of the Full Text search index has no way to optimize both sides of the query according to the Use The Index, Luke!, there is no way to index, or sort the values so that they are optimized with the LIKE operator.

  • 3

    In fact you answered my question and for me the answer is: yes, it is possible: using the MATCH operator in FULL_TEXT - it only works for Myisam tables in mysql < 5.6

  • Exactly, or the innoBD

1

Depending on the application an alternative could be made. Imagine a book sales website Let’s have a clear table of BOOKS (code, name, author, etc.) A table could be created LIVROS_PALAVRA_CHAVE (CODIGO_LIVRO,WORD)

The most important search words would be in this table with the use of index would be an exact search.

The problem is that we users often do not remember the correct spelling, but this nor the LIKE solves.

  • How Search Engines/Spell Checkers Work Then?

  • Here comes the "match.Against" of life that seek closeness.

  • Basically as it was said, optimizing LIKE and the like in general is NOT. And how to look for the glasses when you have no idea where you left off with the aggravating aspect of seeing wrong ...

Browser other questions tagged mysql query operators índices operator-like

You are not signed in. Login or sign up in order to post.