Results 1 to 4 of 4
  1. #1
    Join Date
    May 2002
    Location
    Vancouver Canada
    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. #2
    Join Date
    May 2002
    Location
    Vancouver Canada
    Posts
    26
    I guess we are all stumped on this one...?

  3. #3
    Join Date
    Sep 2002
    Location
    Montreal, Canada
    Posts
    224
    Provided Answers: 1

    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. #4
    Join Date
    May 2002
    Location
    Vancouver Canada
    Posts
    26
    Ha! Actually nevermind now, I put an index on buildingID in the Drawing table and everything works out fine.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •