On Thu, 14 Feb 2008 22:48:32 +0100, Fei Chen <eifench@gmail.com> wrote:

> Hi all,
>
> My join statement is running very slowly and I just want to check with
> the community whether this is expected or not.
>
> I have three tables, table 1 (50K records) contains id1, table 2 (100K
> records) contains id1 and id2, table 3 (50K records) contains id2, I
> want to join table 1 with table 3, through table 2. So I have a join
> statement:
>
> select *

Don't select *, select the fields you want. For instance, if you don't
want anything from table 2, don't select anything from table2.

> from table1 left join table2
> on (table1.id1 = table2.id1) left join table3 on (table2.id2 =
> table3.id2);

Why left join instead of join? You explicitly want all records records of
table 1 regardless of wether they have an entry in table2 and table3?

> If I only join table1 with table2 on id1, the time it takes is only
> about 2 seconds, table 2 with table 3 on id2 takes about 1 second. But
> as soon as I have two joins in the same statement, it took about 12
> hours.

An explain before the query could tell you a lot more about why. It might
well result in an enormous quantity of rows.

> Am I doing something wrong? None of the tables are indexed, could that
> be the problem? I find it really puzzling the difference can be so big
> between joining 2 tables and 3, for what I think to be a pretty standard
> operation. I must be doing something wrong?
>
> Here are the table sizes, I really appreciate any . Cheers.

A show create table would be more .
--
Rik Wasmus