auto_parser wrote:
Glen,
Would you be able to forward the following message to the mysql list. I
keep getting bounce-backs with the following:
Recipient: <mysql@lists.mysql.com>
Reason: Mail from HELO priv-edtnes27.telusplanet.net rejected
because it does not accept bounces. This violates RFC 821/2505/2821
http://www.rfc-ignorant.org/
After contacting rfc-ignorant.org, they assured me it is a mistake in
the way mysql is implementing the RFC protocol, and that I'm not on any
black lists. As I can't contact the list, I can't resolve the issue.
Unfortunately, my query optimization is a more pressing problem at this
moment.
Thanks for your help,
Scott.
************************************************************************
*
Question for MySQL:
Subject: Select query optimization help! 45 seconds+ query...
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.
****************************
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;
--
Gleb Paharenko
[EMAIL PROTECTED]
--- Begin Message ---
Glen,
Would you be able to forward the following message to the mysql list. I
keep getting bounce-backs with the following:
Recipient: <mysql@lists.mysql.com>
Reason: Mail from HELO priv-edtnes27.telusplanet.net rejected
because it does not accept bounces. This violates RFC 821/2505/2821
http://www.rfc-ignorant.org/
After contacting rfc-ignorant.org, they assured me it is a mistake in
the way mysql is implementing the RFC protocol, and that I'm not on any
black lists. As I can't contact the list, I can't resolve the issue.
Unfortunately, my query optimization is a more pressing problem at this
moment.
Thanks for your help,
Scott.
************************************************************************
*
Question for MySQL:
Subject: Select query optimization help! 45 seconds+ query...
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.
****************************
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;
--- End Message ---
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
