The following query is in desperate need of optimization. Any gurus out there who can share some insights, I'd greatly appreciate it.
I have a request table, from which I'm pulling all records. Each request record has a part number, and for each requested part number, I'd like to query the inventory table for all matching inventory items for that part number, and calculate the potential value of the inventory. Each inventory record has 4 price fields, and it is the HIGHEST value of these fields used in the calculation. As you can tell by running the query, it is incredibly expensive. We have potentially millions of inventory records and 10,000+ requests, so the query is going to take several minutes to complete. Also, I found that due to the joining on each record(?), I couldn't implement a limit clause to save time. Whether or not a limit clause is tacked onto the end of the query, it completes the entire calculation process for both tables, and THEN returns the limited number of records. This of course saves me no time. :( Any help is greatly appreciated, Thanks. ps, all pertinent fields have already been indexed. This reduced the query time by half, but half of an eternity is still not that impressive. I'm also aware that the use of LIKE in my JOIN is expensive, but after replacing it with a =, I achieved only modest performance gains. **************************** SELECT r.id, r.company, r.dateSent, r.fullName, r.phone, r.fax, r.email, r.address1, r.address2, r.city, r.province, r.country, r.comments, r.partNumber, r.description, r.dateCode, r.qty, r.targetPrice, r.manufacturer, r.expiryDate, r.companyType, r.yearEstablished, r.url, r.languages, GREATEST(i.distySellCost, i.originalCost,i.unitCost,i.unitSellCost)*r.qty AS 'highestValue', count(i.id) as 'matches', SUM(i.qty) as 'qtyAvailable' FROM request r LEFT JOIN inventory i ON ( i.MPN LIKE CONCAT(r.partNumber, '%') OR i.MPNClean LIKE CONCAT(r.partNumber, '%')) AND i.status=1 WHERE r.deleted=0 GROUP BY r.id; Any help is appreciated. Thanks. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]