Mike, I used Explain and I don't see anything that says indexes. I use the Control Center for all of my stuff. Would it show in there? 88 rows are returned both in mySQL and Access.
>>> Brent Baisley <[EMAIL PROTECTED]> 1/26/2004 2:03:10 PM >>> How did you determine the bottleneck was the left join? I would have thought it was the WHERE clause. I'd be curious what the times are on these queries: SELECT count(*) FROM Products LEFT JOIN tblInvTransaction ON Products.ProductID = tblInvTransaction.ProductID Where ((NSIPartNumber Like "%02-001%") AND (Obsolete<>-1)) and SELECT count(*) FROM Products LEFT JOIN tblInvTransaction ON Products.ProductID = tblInvTransaction.ProductID As far as Access comparison, you must compare it to a multi-user Access database hosted on the network with at least 1 other client having the database open, but it can be idle. Comparing it to a local Access database is not a comparable setup. On Jan 26, 2004, at 1:35 PM, Jacque Scott wrote: > I have a fairly simple query where I feel it takes too long to run. > > SELECT Products.NSIPartNumber,Products.Cost, Products.ModelNo, > Products.USPrice, Products.VendorPart, Products.Description , > Products.ProductID, Sum(tblInvTransaction.Qty) AS SumOfQty FROM > Products > LEFT JOIN tblInvTransaction ON Products.ProductID = > tblInvTransaction.ProductID Where ((NSIPartNumber Like "%02-001%") AND > (Obsolete<>-1)) Group BY NSIPartNumber; > > By running reduced versions of this query I have narrowed down the > problem to the 'Left Join' clause. There are 6852 records in the > Products table and 45758 records in the tblInvTransaction table. To > run the above query it takes approx. 15 seconds to run in mySQL and if > I > run the same query in Access 2.0 it takes approx 5 seconds. > > What am I doing wrong? > -- Brent Baisley Systems Architect Landover Associates, Inc. Search & Advisory Services for Advanced Technology Environments p: 212.759.6400/800.759.0577
