Jump to content

Archived

This topic is now archived and is closed to further replies.

MarkH

SQL using "like" is really slow??

Recommended Posts

Hi All,

I'm having a problem with MySQL performing really badly.

 

if i do a select where we have a join and we are just looking for the search parameter in one of the joined tables, then the query only takes about 1 second:

$res = mysql_query("
 	SELECT * FROM logs, updates
 	WHERE logs.feedback_number = updates.feedback_number
 	AND update_details LIKE '%$like%'
 	GROUP BY logs.feedback_number
 	ORDER BY logs.feedback_number DESC
 	"

 

similarly, if i look for the search term matching against fileds in the other table, again it only takes about a second

$res = mysql_query("SELECT * FROM logs 
 	WHERE feedback_number LIKE '%$like%' 
 	OR feedback_description LIKE '%$like%' 
 	OR product LIKE '%$like%' 
 	OR product_serial LIKE '%$like%' 
 	OR customer_name LIKE '%$like%' 
 	OR customer_contact LIKE '%$like%' 
 	OR contact_telephone LIKE '%$like%' 
 	OR contact_email LIKE '%$like%' 
 	OR change_request_num LIKE '%$like%'
 	ORDER BY feedback_number DESC"

 

but if i try to combine the 2 statements so that in effect it's doing a similar job to both the above combined, it now takes half a minute to return the answers!!??

I was presuming that as both the above took around a second, then the combined search should only take a couple of seconds???

Is it just the case that using too many "LIKE" statements is inefficient??

$res = mysql_query("
 	SELECT * FROM logs, updates
 	WHERE logs.feedback_number = updates.feedback_number
 	AND updates.update_details LIKE '%$like%'
 	OR logs.feedback_number = '$like' 
 	OR logs.feedback_description LIKE '%$like%' 
 	OR logs.product = '$like' 
 	OR logs.product_serial = '$like' 
 	OR logs.customer_name LIKE '%$like%' 
 	OR logs.customer_contact LIKE '%$like%' 
 	OR logs.contact_telephone = '$like' 
 	OR logs.contact_email = '$like' 
 	OR logs.change_request_num = '$like'
 	GROUP BY logs.feedback_number
 	ORDER BY logs.feedback_number DESC
 	"

 

please help! :-)

Share this post


Link to post
Share on other sites

ok, to answer myself, i found if i put parenthesis around the "OR" section, it suddenly jumps to hyper speed :-)

 

$res = mysql_query("
 	SELECT * FROM logs, updates
 	WHERE logs.feedback_number = updates.feedback_number
 	AND (updates.update_details LIKE '%$like%'
 	OR logs.feedback_number = '$like' 
 	OR logs.feedback_description LIKE '%$like%' 
 	OR logs.product = '$like' 
 	OR logs.product_serial = '$like' 
 	OR logs.customer_name LIKE '%$like%' 
 	OR logs.customer_contact LIKE '%$like%' 
 	OR logs.contact_telephone = '$like' 
 	OR logs.contact_email = '$like' 
 	OR logs.change_request_num = '$like')
 	GROUP BY logs.feedback_number
 	ORDER BY logs.feedback_number DESC"

Share this post


Link to post
Share on other sites

×

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.