PHP Developers Network

A community of PHP developers offering assistance, advice, discussion, and friendship.
 
Loading
It is currently Tue Dec 11, 2018 11:14 pm

All times are UTC - 5 hours




Post new topic Reply to topic  [ 4 posts ] 
Author Message
PostPosted: Wed Sep 16, 2009 2:08 am 
Offline
Forum Regular
User avatar

Joined: Tue Sep 25, 2007 8:36 am
Posts: 521
Location: Greece
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?


Top
 Profile  
 
PostPosted: Wed Sep 16, 2009 2:39 am 
Offline
DevNet Resident
User avatar

Joined: Tue Mar 16, 2004 11:03 am
Posts: 1425
Location: Aachen, Germany
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.


Top
 Profile  
 
PostPosted: Wed Sep 16, 2009 2:45 am 
Offline
DevNet Master
User avatar

Joined: Fri Jan 18, 2008 1:36 am
Posts: 3549
Location: Israel, ME
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.


Top
 Profile  
 
PostPosted: Mon Sep 28, 2009 2:44 am 
Offline
DevNet Master

Joined: Wed Feb 11, 2004 4:23 pm
Posts: 4872
Location: Palm beach, Florida
This belongs in the database forums :roll:


Top
 Profile  
 
Display posts from previous:  Sort by  
Post new topic Reply to topic  [ 4 posts ] 

All times are UTC - 5 hours


Who is online

Users browsing this forum: No registered users and 2 guests


You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot post attachments in this forum

Jump to:  
Powered by phpBB® Forum Software © phpBB Group