Is this the way to set Indexes? It sound like a good first approximation. To go beyond that, you need to think a bit about what MySQL will do to satisfy the query. Access, and any other database manager, would do similar things--just that the tradeoffs chosen may be different. For example, if you were going to run this query one once a month, you might live with the 45 seconds. On the other hand, for a query you are going to run a lot, you might want to include extra fields in the index so that MySQL can just read the index instead of reading the actual records. Remember that it costs something to maintain the index, too. (Time to look for a book on SQL that talks about such things...) ----- Original Message ----- From: Jacque Scott To: [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Sent: Thursday, January 29, 2004 6:28 PM Subject: Re: More Left Join problems
Well setting indexes of both tables helped. The query now runs at 0.38 seconds. What a difference. Is this the way to set Indexes? Set a 'Unique' index for the PK of each table and just an 'Index' for the foreign keys in each table. Bill, You have been a life saver. Thank you so much. I have used Access the last few years and it is a different way of thinking. Jacque >>> "Bill Easton" <[EMAIL PROTECTED]> 1/29/2004 1:13:00 PM >>> You need an index on BOM.ProductID try: alter table BOM add index (ProductID); then run your query again Some additional notes on your query: (1) You have an expression involving BOM.ProductID in your field list. Since that column is not in the group by clause, you'll get a ProductID from some record in the group--no way to predict which one. (Unless, of course, all rows a given NSIPartNumber has the same ProductID.) Note that ANSI SQL would not let you have this column in the field list and not in the group by. If you have an NSIPartNumber for which some ProductID's have a corresponding BOM record and some don't, you'll get a "x" or not unpredictably, at MySQL's whim. If you want to know if there is ANY ProductID for an NSIPartNumber without a corresponding record, you could use an expression involving something like MAX(BOM.ProductID IS NULL), which will be 1 if there is one and 0 if there isn't. (2) You probably want the stuff in your HAVING clause to be in a WHERE clause instead. HAVING means that the entire result set is generated and then filtered again to remove some records--although MySQL may optimize it so it doesn't really do that. (Just change HAVING to WHERE and move it before the GROUP BY.) It's good practice to use the where clause unless you really need having. You might really need having if you are selecting from the result based on group functions such as max() or count()--then, the values you are testing aren't available for the where clause to use. ----- Original Message ----- From: Jacque Scott To: [EMAIL PROTECTED] ; [EMAIL PROTECTED] Sent: Thursday, January 29, 2004 2:18 PM Subject: Re: More Left Join problems 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
