How resource intensive %LIKE% statement is?

Discussion of testing theory and practice, including methodologies (such as TDD, BDD, DDD, Agile, XP) and software - anything to do with testing goes here. (Formerly "The Testing Side of Development")

Moderator: General Moderators

Post Reply
User avatar
Sindarin
Forum Regular
Posts: 521
Joined: Tue Sep 25, 2007 8:36 am
Location: Greece

How resource intensive %LIKE% statement is?

Post 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?
User avatar
CoderGoblin
DevNet Resident
Posts: 1425
Joined: Tue Mar 16, 2004 10:03 am
Location: Aachen, Germany

Re: How resource intensive %LIKE% statement is?

Post 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.
User avatar
Eran
DevNet Master
Posts: 3549
Joined: Fri Jan 18, 2008 12:36 am
Location: Israel, ME

Re: How resource intensive %LIKE% statement is?

Post 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.
josh
DevNet Master
Posts: 4872
Joined: Wed Feb 11, 2004 3:23 pm
Location: Palm beach, Florida

Re: How resource intensive %LIKE% statement is?

Post by josh »

This belongs in the database forums :roll:
Post Reply