Jump to content


Photo

SQL using "like" is really slow??


  • Please log in to reply
1 reply to this topic

#1 MarkH

MarkH
  • New Members
  • Pip
  • Newbie
  • 2 posts

Posted 26 September 2005 - 12:28 PM

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! :-)

#2 MarkH

MarkH
  • New Members
  • Pip
  • Newbie
  • 2 posts

Posted 26 September 2005 - 02:09 PM

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"





0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users