MySQL Forums
Forum List  »  PHP

Re: Complex search - multiple fields and tables - need advice.
Posted by: laptop alias
Date: April 18, 2012 05:32PM

LIKE is a tricky one. Consider statements 1 and 2 below:

1. c.address2 LIKE '%sunset%'

2. c.address2 LIKE 'sunset%'

Statement 1 cannot use an index but statement 2 can!

As a consequence, there's probably not much more you can do to improve query performance - although you may want to explore MATCH... AGAINST... in the context of FULLTEXT indices. Then again, with a query time of just a fraction if a second, you probably don't need to bother. Still, I'd be tempted to rewrite the query as follows, removing that redundant table, and the improper use of a GROUP BY clause...
     , c.first_name
     , c.last_name
     , c.email_address
     , c.rating
     , c.date_created
     , cej.assigned_dt 
  FROM contacts c 
  JOIN contact_employee_join cej 
    ON cej.contact_id = c.contact_id
  JOIN contact_notes cn 
    ON cn.contact_id = c.contact_id 
  JOIN contact_communications cc 
    ON cc.contact_id = c.contact_id
 WHERE c.first_name LIKE '%sunset%' 
    OR c.last_name  LIKE '%sunset%' 
    OR c.address1   LIKE '%sunset%' 
    OR c.address2   LIKE '%sunset%' 
    OR       LIKE '%sunset%' 
    OR cn.note      LIKE '%sunset%' 
    OR cc.notes     LIKE '%sunset%'
    BY c.last_name
     , c.first_name ASC 
 LIMIT 0,20;

Options: ReplyQuote

Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.