List:General Discussion« Previous MessageNext Message »
From:Dan Nelson Date:October 3 2000 3:50pm
Subject:Re: MySQL 3.23.25 released
View as plain text  
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
Thread
MySQL 3.23.25 releasedMichael Widenius28 Sep
  • RE: MySQL 3.23.25 releasedSander Pilon28 Sep
    • RE: MySQL 3.23.25 releasedMichael Widenius29 Sep
      • RE: MySQL 3.23.25 releasedSander Pilon29 Sep
  • Re: MySQL 3.23.25 releasedCarsten Gehling3 Oct
    • Re: MySQL 3.23.25 releasedBenjamin Pflugmann3 Oct
      • Re: MySQL 3.23.25 releasedMichael Widenius4 Oct
  • Re: MySQL 3.23.25 releasedArtem Koutchine3 Oct
  • Re: MySQL 3.23.25 releasedCarsten Gehling3 Oct
    • Re: MySQL 3.23.25 releasedDan Nelson3 Oct
    • Re: MySQL 3.23.25 releasedPaul DuBois3 Oct
    • Re: MySQL 3.23.25 releasedBenjamin Pflugmann3 Oct
  • Re: MySQL 3.23.25 releasedCarsten Gehling4 Oct
  • Re: MySQL 3.23.25 releasedCarsten Gehling4 Oct