Skip navigation links

MySQL Forums :: PHP :: Complex search - multiple fields and tables - need advice.


Advanced Search

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...
SELECT DISTINCT
       c.contact_id
     , c.first_name
     , c.last_name
     , c.email_address
     , c.city
     , 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 c.city       LIKE '%sunset%' 
    OR cn.note      LIKE '%sunset%' 
    OR cc.notes     LIKE '%sunset%'
 ORDER 
    BY c.last_name
     , c.first_name ASC 
 LIMIT 0,20;

Options: ReplyQuote


Subject Written By Posted
Complex search - multiple fields and tables - need advice. Luke Pittman 04/17/2012 04:49PM
Re: Complex search - multiple fields and tables - need advice. laptop alias 04/18/2012 05:05AM
Re: Complex search - multiple fields and tables - need advice. Luke Pittman 04/18/2012 10:31AM
Re: Complex search - multiple fields and tables - need advice. Luke Pittman 04/18/2012 11:19AM
Re: Complex search - multiple fields and tables - need advice. Luke Pittman 04/18/2012 11:24AM
Re: Complex search - multiple fields and tables - need advice. laptop alias 04/18/2012 05:32PM
Re: Complex search - multiple fields and tables - need advice. Luke Pittman 04/19/2012 12:43PM
Re: Complex search - multiple fields and tables - need advice. laptop alias 04/19/2012 01:11PM
Re: Complex search - multiple fields and tables - need advice. Luke Pittman 04/19/2012 01:15PM


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.