Re: FN-FORUM: Help/guidance with a bit of tricky php/MySQL

date posted 4th March 2008 14:25

Depends on volumes of data.

With a sub-select, you are effectively running that query first and
using the results as the "IN" clause. Obviously, on a large table, that
"IN" could be pretty big. You wouldn't, for instance, write that query
and type in thousands of values in the IN clause - would you?

Run an explain plan and you will see table scans when using the IN
clause, where the join will use an index if one exists and is more
efficient to use.

So, I guess the IN clause is fine for small lists of returned values.
The join will be better if there are large volumes. The join will be
more scalable as the database grows - which should always be part of
your design decision.

The other syntax (in Oracle at least) is the EXISTS clause.

There's always more than one way to skin a cat - as they say :)

Dom Latter wrote:
> On Tuesday 04 March 2008 15:37:13 Duncan Glendinning wrote:
>
>> Question is, which is most efficient?
>
> [subselect vs. join]
>
> Everything I've read suggests that joining is much more efficient.
>
> You can try running an EXPLAIN on the queries for more info.
>
>
>

--
Dominic Raywood
Rubicon Computer Services Limited
W: http://rubiconcsl.com
E: [EMAIL REMOVED]
T: 08456 521 561 (+44 1823 698420)
F: 08456 521 561
M: 07050 204998 (+44 7811 252573)
Skype: RubiconCSL