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]
