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?
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
– Leo
A relevant article on the scalability of
LIKE
andFULLTEXT
: http://makandracards.com/makandra/12813-performance-analysis-of-mysql-s-fulltext-indexes-and-like-queries-for-full-text-search– utluiz
You could migrate this type of search to a search engine, maybe Solr (http://lucene.apache.org/solr/)
– jtomaszon
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.
– Oralista de Sistemas