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

Thank you for the reply.

Outer Joins with where conditions making them Inner joins. Well, I *think* this is what I want. An example search is shown below (with the "Explain" results below it as requested). Correct me if I am wrong - but an Outer join would join all the table regardless if a result is given or not, and an inner join will only join when a match is made. So, yes I only need the Inner join.

Regarding this line:

($date_field BETWEEN '$date_from' AND '$date_to')

The SQL statement is generated using PHP based form input and a few other things, part of which is a date function. The user can select which "type" of date to search (date assigned or date created - $date_field), a start date ($date_from) and an end date ($date_to). The PHP script essentially analyses the input and assigns the fields accordingly. In most cases this part of the search will not be used (as in my example below) but occasionally it may be used. I can perform a search using this as well and provide more information if you think its necessary. However I believe if I can speed up the join part of it adding a date constraint should only speed it up?

I have provided the EXPLAIN output below. I have tried to use this to tweak things myself, however I don't fully grasp what it is telling me yet. I've been reading up on it, but between this and studying for my final exams I have so much in my head that nothing is getting retained any more. Heh.

Thanks.

Luke



Statement

SELECT `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` 
LEFT JOIN `contact_employee_join` `cej` ON `c`.`contact_id` = `cej`.`contact_id` 
LEFT JOIN `employee_locations` `el` ON `cej`.`employee_id` = `el`.`employee_id` 
LEFT JOIN `contact_notes` `cn` ON `c`.`contact_id` = `cn`.`contact_id` 
LEFT JOIN `contact_communications` `cc` ON `c`.`contact_id` = `cc`.`contact_id` 
WHERE (`c`.`first_name` LIKE '%sunset%' || 
  `c`.`last_name` LIKE '%sunset%' || 
  `c`.`address1` LIKE '%sunset%' || 
  `c`.`address2` LIKE '%sunset%' || 
  `c`.`city` LIKE '%sunset%' || 
  `cn`.`note` LIKE '%sunset%' || 
  `cc`.`notes` LIKE '%sunset%') 
GROUP BY `c`.`contact_id` 
ORDER BY `c`.`last_name`, `c`.`first_name` ASC LIMIT 0,20

Explain Output

Array
(
    [id] => 1
    [select_type] => SIMPLE
    [table] => c
    [type] => ALL
    [possible_keys] => 
    [key] => 
    [key_len] => 
    [ref] => 
    [rows] => 3816
    [Extra] => Using temporary; Using filesort
)
Array
(
    [id] => 1
    [select_type] => SIMPLE
    [table] => cej
    [type] => ref
    [possible_keys] => PRIMARY,contact_id
    [key] => contact_id
    [key_len] => 4
    [ref] => eaglehomes.c.contact_id
    [rows] => 1
    [Extra] => 
)
Array
(
    [id] => 1
    [select_type] => SIMPLE
    [table] => el
    [type] => ref
    [possible_keys] => PRIMARY
    [key] => PRIMARY
    [key_len] => 4
    [ref] => eaglehomes.cej.employee_id
    [rows] => 1
    [Extra] => Using index
)
Array
(
    [id] => 1
    [select_type] => SIMPLE
    [table] => cn
    [type] => ALL
    [possible_keys] => 
    [key] => 
    [key_len] => 
    [ref] => 
    [rows] => 973
    [Extra] => 
)
Array
(
    [id] => 1
    [select_type] => SIMPLE
    [table] => cc
    [type] => ALL
    [possible_keys] => 
    [key] => 
    [key_len] => 
    [ref] => 
    [rows] => 4321
    [Extra] => Using where
)