How resource intensive %LIKE% statement is?
Moderator: General Moderators
How resource intensive %LIKE% statement is?
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?
- CoderGoblin
- DevNet Resident
- Posts: 1425
- Joined: Tue Mar 16, 2004 10:03 am
- Location: Aachen, Germany
Re: How resource intensive %LIKE% statement is?
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.
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?
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.
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?
This belongs in the database forums 