# Thread: INNER JOIN works fine but LEFT JOIN takes forever...?

1. Registered User
Join Date
May 2002
Location
Posts
26

## Unanswered: INNER JOIN works fine but LEFT JOIN takes forever...?

I have two tables I want to join in different ways to obtain different results. When I do an INNER JOIN everything works fine, but a LEFT JOIN takes 14 minutes to return the result. This seems strange to me because the left join does not return many more rows than the inner join.

The two tables are named Building and Drawing. There are 7459 rows in the Building table and 11,270 rows in the Drawing table.

The relationship between them is that a building can have 0 to many drawings, and a drawing goes with one and only one building.

The INNER JOIN returns 11,270 rows, like it should (because there are 11,270 drawings), and takes 0.48 seconds.

The LEFT JOIN returns 11,691, only 421 more rows than the inner join, and takes over 14 minutes.

The LEFT JOIN query does not return a significantly larger number of rows. Why does it take so long then?

SELECT *
FROM Building
INNER JOIN Drawing ON Drawing.buildingID = Building.buildingID;

SELECT *
FROM Building
LEFT JOIN Drawing ON Drawing.buildingID = Building.buildingID;

2. Registered User
Join Date
May 2002
Location
Posts
26
I guess we are all stumped on this one...?

3. Registered User
Join Date
Sep 2002
Location
Posts
228

## Re: INNER JOIN works fine but LEFT JOIN takes forever...?

Originally posted by Dylan Leblanc
I have two tables I want to join in different ways to obtain different results. When I do an INNER JOIN everything works fine, but a LEFT JOIN takes 14 minutes to return the result. This seems strange to me because the left join does not return many more rows than the inner join.

The two tables are named Building and Drawing. There are 7459 rows in the Building table and 11,270 rows in the Drawing table.

The relationship between them is that a building can have 0 to many drawings, and a drawing goes with one and only one building.

The INNER JOIN returns 11,270 rows, like it should (because there are 11,270 drawings), and takes 0.48 seconds.

The LEFT JOIN returns 11,691, only 421 more rows than the inner join, and takes over 14 minutes.

The LEFT JOIN query does not return a significantly larger number of rows. Why does it take so long then?

SELECT *
FROM Building
INNER JOIN Drawing ON Drawing.buildingID = Building.buildingID;

SELECT *
FROM Building
LEFT JOIN Drawing ON Drawing.buildingID = Building.buildingID;
Can you give the tables structure?
Can you give the EXPLAIN of those statements?

4. Registered User
Join Date
May 2002
Location