mem bob wrote:
> | id | model | service_id |
> |----|-------|------------|
> | 1 | 500 | 1 |
> | 2 | 500 | 3 |
> | 3 | 500 | 10 |
> | 4 | 600 | 1 |
>
>>From this table i want to extract all distinct models which have
> service_id=1 *AND* service_id=3
The JOIN-construction is something like:
SELECT `model` FROM `i` AS t1 JOIN `i` AS t2 ON t1.`model` = t2.`model`
AND t1.`id` != t2.`id` WHERE t1.`service_id` = 1 AND t2.`service_id` = 3
GROUP BY t1.`model`;
Experiment to see whether it makes a difference of not if you move the
expressions in the WHERE clause to the ON clause of the JOIN. It seems
to depend on the version of MySQL that you are using how good the
optimizer is at finding out the fastest execution path.
Make sure that you have an index on at least `model` and perhaps a
combined index on `model`, `id` and/or `service_id`. Experiment with
indexes and see which combination is the fastest.
Look at the output from EXPLAIN <query> to see the type of table joins
MySQL will use and how many records it estimates it needs to evaluate in
each step.
Regards, Jigal.
