SitePoint Sponsor

User Tag List

Results 1 to 4 of 4
  1. #1
    SitePoint Zealot
    Join Date
    Sep 2005
    Posts
    199
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    slow execution for a left outer join query

    Hi guys

    Whats likely to be the cause of slow execution for a left outer join query?

    The original query joins three tables but even if I narrow it down to one it still takes a long time to execute.

    $query = "select distinct materials.* from materials";
    $query .= " left outer join materials_products on materials.material_id = materials_products.material_id";

    There's 914 rows in the materials table and 1348 row in the materials_products table

    Is it likely to take a long time for this amount of data or is there likely to be a problem in the table(s) set up or query?

    Thanks in advance

    T

  2. #2
    reads the ********* Crier silver trophybronze trophy longneck's Avatar
    Join Date
    Feb 2004
    Location
    Tampa, FL (US)
    Posts
    9,854
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    do you have an index on materials_products.material_id?

    however, i have a feeling the problem is more likely your combination of distinct and *, and the fact that the LEFT OUTER JOIN in your case is completely useless.

    your query, as written, is equivalent to select * from materials

  3. #3
    SitePoint Zealot
    Join Date
    Sep 2005
    Posts
    199
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hi longneck

    Thank you for your reply.

    Yer this query doesn't really make sense to me as this is someone elses code

    This is the whole query he used

    select distinct materials.* from materials
    left outer join materials_products on materials.material_id = materials_products.material_id
    left outer join products on materials_products.product_id = products.product_id
    left outer join brands on products.brand_id = brands.brand_id
    where brand_name!='name' or brand_name is null
    order by material_ref

    But isn't that just the same as;

    SELECT * FROM materials_products mp
    INNER JOIN materials m ON mp.material_id = m.material_id
    INNER JOIN products p ON mp.product_id = p.product_id
    LEFT JOIN brands b ON p.brand_id = b.brand_id
    where brand_name!='name' or brand_name is null
    order by material_ref

    Although my attemp is returning more results I'm really not sure what he was trying to achieve??

    Any thoughts??

    Thanks again for your reply

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,220
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    no, it isn't the same

    using LEFT OUTER JOINs, you are returning all rows from the materials table, whether they have any associated rows in the materials_products, products, and brands tables or not, and then simply taking those distinct rows

    as longneck said, this is equivalent to select * from materials

    with INNER JOINs, you're returning only those rows that do have matching associated rows in the materials_products, products, and brands tables, but returning each materials row multiple times, once for every match
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


Bookmarks

Posting Permissions

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