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 * from table1 left join table2 on (table1.id1 = table2.id1) left
join table3 on (table2.id2 = table3.id2);

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.

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.

mysql> select count(distinct(id1))from table1;
+-------+
| id1 |
+-------+
| 59134 |
+-------+

mysql> select count(distinct(id1)) from table2;
+-------+
| id1 |
+-------+
| 76643 |
+-------+

mysql> select count(distinct(id2)) from table2;
+--------+
| id2 |
+--------+
| 101158 |
+--------+

mysql> select count(distinct(id2)) from table3;
+-------+
| id2 |
+-------+
| 50261 |
+-------+

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

Thanks Rik for your prompt reply. The statement I had put down was just an
example and probably not very well constructed. But I'm not sure if the
speed issue is due to "select *" or left join, as the same slowness
results from

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

> An explain before the query could tell you a lot more about why.

not sure what you mean here... basically what I want to do is to
link/paste/join records in table1 with those in table3 that have the same
ids...


On Thu, 14 Feb 2008, Rik Wasmus wrote:

> 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 .
>

On Thu, 14 Feb 2008 23:47:16 +0100, Fei Chen <eifench@gmail.com> wrote:
> On Thu, 14 Feb 2008, Rik Wasmus wrote:
>
>> 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 havea
>>> 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 .


>>> Thanks Rik for your prompt reply.

Thank me by not topposting.

> The statement I had put down was just an example and probably not very
> well constructed. But I'm not sure if the speed issue is due to "select
> *" or left join, as the same slowness results from
>
> select table1.id1 from table1 join table2 on (table1.id1 = table2.id1)
> join table3 on (table2.id2 = table3.id2);
>
>> An explain before the query could tell you a lot more about why.
>
> not sure what you mean here... basically what I want to do is to
> link/paste/join records in table1 with those in table3 that have the
> same ids...

Run these queries, and show us the results:

EXPLAIN SELECT table1.id1 FROM table1
JOIN table2 ON table1.id1 = table2.id1
JOIN table3 ON table2.id2 = table3.id2;

SHOW FULL COLUMNS FROM table1;
SHOW FULL COLUMNS FROM table2;
SHOW FULL COLUMNS FROM table3;
--
Rik Wasmus

Problem solved. Thanks Rik, the problem was indeed improper indexing.

On Fri, 15 Feb 2008, Rik Wasmus wrote:

> On Thu, 14 Feb 2008 23:47:16 +0100, Fei Chen <eifench@gmail.com> wrote:
>> On Thu, 14 Feb 2008, Rik Wasmus wrote:
>>
>>> 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 .
>
>
>>>> Thanks Rik for your prompt reply.
>
> Thank me by not topposting.
>
>> The statement I had put down was just an example and probably not very well
>> constructed. But I'm not sure if the speed issue is due to "select *" or
>> left join, as the same slowness results from
>>
>> select table1.id1 from table1 join table2 on (table1.id1 = table2.id1) join
>> table3 on (table2.id2 = table3.id2);
>>
>>> An explain before the query could tell you a lot more about why.
>>
>> not sure what you mean here... basically what I want to do is to
>> link/paste/join records in table1 with those in table3 that have the same
>> ids...
>
> Run these queries, and show us the results:
>
> EXPLAIN SELECT table1.id1 FROM table1
> JOIN table2 ON table1.id1 = table2.id1
> JOIN table3 ON table2.id2 = table3.id2;
>
> SHOW FULL COLUMNS FROM table1;
> SHOW FULL COLUMNS FROM table2;
> SHOW FULL COLUMNS FROM table3;
>

Fei Chen wrote:
> On Fri, 15 Feb 2008, Rik Wasmus wrote:
>
>
> Problem solved. Thanks Rik, the problem was indeed improper indexing.
>
And as Rik said in an earlier post, please show your thanks in future by NOT
top posting! (top posting fixed)