In the last episode (Oct 03), Carsten Gehling said:
> From: "Benjamin Pflugmann" <philemon@stripped>
> Sent: Tuesday, October 03, 2000 4:45 PM
>
> > Maybe I completely misunderstand you, but what is wrong with the
> > plain JOIN?
> >
> > SELECT * FROM table1, table2 WHERE table1.id = table2.id
> >
> > results in exactly what you ask for, doesn't it?
>
> Yes, but the performance is poor. The above statement makes a
> cartesian product, where every record in table1 is combined with
> every record in table2. Only after this cartesian product is
> completed, are the correct records selected and returned.
No; mysql does not do a cartesian product here. It simply walks
table1, and for each id, it looks up the appropriate entry(s) in table2
by keying off the id index. Try doing an "EXPLAIN SELECT ..." to see
what mysql ends up doing. If table2 is smaller than table1, it might
actually use table2 as the driving table.
> 200*200*200 = 8.000.000 records in the temporary table. Before the where
> statement is executed.
Actually, the where statement is used by mysql to figure out which
indexes to use to join the tables together. You might be thinking of
the HAVING clause, which is only used after all records have been
fetched.
--
Dan Nelson
dnelson@stripped
