Page 1 of 1

How resource intensive %LIKE% statement is?

Posted: Wed Sep 16, 2009 2:08 am
by Sindarin
I use SQL %LIKE% statement for my search fields, I heard that %LIKE% is very resource intensive. Can someone confirm this? Are there any tests? Are there any alternatives?

Re: How resource intensive %LIKE% statement is?

Posted: Wed Sep 16, 2009 2:39 am
by CoderGoblin
One of the questions is obviously why are you using "like"? Without putting it in some context it is hard to know how to avoid it. It may be sanitizing the information on data entry may avoid the need to use it.

I have never done tests but have also been told to avoid it where possible due to being resource intensive and it makes sense to be so.

One possible alternative depending on the situation is full text searching. Not sure how it works on MySQL though.

Re: How resource intensive %LIKE% statement is?

Posted: Wed Sep 16, 2009 2:45 am
by Eran
LIKE with wildcards on both sides of a string cannot use an index. For that reason it has to scan every row to find matches. Depending on the size of your table, this can be very slow. A few hundreds to a few thousands rows would still complete in reasonable time, any more than that and LIKE will not give good performance.

If you can avoid the first wildcard (at the beginning) you can use an index to help the search. Alternatively, you can use a fulltext index and functions. Or use a dedicated text indexing engine, such as sphinx or lucene, that give much better performance and scalability compared to MySQL native abilities.

Re: How resource intensive %LIKE% statement is?

Posted: Mon Sep 28, 2009 2:44 am
by josh
This belongs in the database forums :roll: