Re: Complex search - multiple fields and tables - need advice.

[laptop alias]

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;