Thanks for your time. I didn't think of formatting the query. Here is the query in a more readable format. I have also taken out most of the columns in the SELECT. The query still takes 44 seconds. SELECT Products.NSIPartNumber, If(Bom.ProductID Is Not Null,"x","") AS BOM, Products.Obsolete FROM Products LEFT JOIN BOM ON Products.ProductID = BOM.ProductID GROUP BY Products.NSIPartNumber, Products.Obsolete HAVING ((NSIPartNumber Like "%02-001%") AND (Obsolete<>-1)); Hopefully this will be easier to decipher.
>>> "Bill Easton" <[EMAIL PROTECTED]> 1/29/2004 10:34:21 AM >>> Jacque, Based on your explain, I'd guess that you don't have any indexes. Probably, you need (at least) an index on the join column on the second table, as, otherwise, MySQL will read the second table once for each row of the first table. This probably doesn't have anything to do with the fact that it's a left join. You'd probably get the same result with an inner join, since the tables appear to be about the same size. Some hints on reading the explain: The "ALL" means MySQL is going to read the whole table instead of using an index. The [NULL]'s mean there is no available index. The filesort means it's going to get the content of the table and sort it. (You could get rid of that with an index, but it's not all bad, unless your data is already stored in approximately the correct order.) I haven't tried to decipher your query. It's a big, unreadable blob, and I'm not willing to put in the time. People posting to this list would do well to reformat and indent example queries so that they are easy for a human to read. It would also be good to make the examples as short as possible. I probably saw and ignored your earlier question for that reason; I usually just skip messages where it would take too long to decipher the question--I'd assume that other people do the same. HTH Bill > Date: Thu, 29 Jan 2004 08:03:25 -0800 > From: "Jacque Scott" < [EMAIL PROTECTED] > > To: < [EMAIL PROTECTED] > > Subject: More Left Join problems > I had posted a message earlier this week about my 'Left Join' taking too > long to run. This seems to be happening on all of my queries that have > a 'Left Join'. Does anyone have any suggestions on why this would > happen? > > Here is one query which took 45.72 sec to run: > > SELECT Products.NSIPartNumber, Products.Cost AS Cost, If(Bom.ProductID > Is Not Null,"x","") AS BOM, Products.lngLaborHrs AS LaborHrs, > Products.ModelNo, Products.USPrice AS USPrice, Products.VendorPart, > Products.Description, Products.ProductID, Null AS SumOfQty, > Products.Obsolete FROM Products LEFT JOIN BOM ON Products.ProductID = > BOM.ProductID GROUP BY Products.NSIPartNumber, Products.Cost, > If(Bom.ProductID Is Not Null,"x",""), Products.lngLaborHrs, > Products.ModelNo, Products.USPrice, Products.VendorPart, > Products.Description, Products.ProductID, Products.Obsolete Having > ((NSIPartNumber Like "%02-001%") AND (Obsolete<>-1)) ORDER BY > NSIPartNumber; > > > Here is the results when I use Explain. I don't really know how to > read this. > +--------+-------------+--------+--------+---------------+--------+--------- +--------+--------+--------+ > | id | select_type | table | type | possible_keys | key | > key_len | ref | rows | Extra | > +--------+-------------+--------+--------+---------------+--------+--------- +--------+--------+--------+ > | 1 | SIMPLE | Products| ALL | [NULL] | [NULL] | > [NULL] | [NULL] | 6852 | Using temporary; Using filesort| > | 1 | SIMPLE | BOM | ALL | [NULL] | [NULL] | > [NULL] | [NULL] | 5995 | | > +--------+-------------+--------+--------+---------------+--------+--------- +--------+--------+--------+ > Any help is appreciated. > > Jacque
