I would also question the sub-select in the WHERE clause. I suspect that it
is being evaluated once for each row of rptPricingTEST. You could get a
significant performance boost if you move the results of that select into a
temp table and INNER JOIN to it. That way you calculate the MAX() date only
once for the entire table and not once per row (per row, per row,...)
CREATE TEMPORARY TABLE tmpLatestDates
SELECT commodity, MAX(PricingDt) as MaxDate
FROM rptPricingTEST
WHERE PricingDt > @date1
AND PricingDt <= @date2
GROUP BY commodity
INSERT INTO priceLast5
SELECT DISTINCT a.commodity, a.PricingDt
FROM rptPricingTEST a
INNER JOIN tmpLatestDates b
ON b.commodity = a.commodity
AND b.PricingDt = a.PricingDt
(By using the INNER JOIN, I question if the DISTINCT still needed, too.)
After reviewing your original post, I am not confident that you are
answering your question with this query. You said "I need to generate a
report of price change for all commodity for the date interval selected by
users." which to me implies a report something like:
+---------+-------------+-----------+--------------+------------+---------+---------+---------+
|commodity|starting date|ending date|starting price|ending price|max
price|min price|avg price|
+---------+-------------+-----------+--------------+------------+---------+---------+---------+
I am just not sure where you are going with the query you are building. Can
you provide a template of the results you want?
Thanks,
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine
"Andrew
Braithwaite" To: "Victor Pendleton" <[EMAIL
PROTECTED]>, "nyem "
<[EMAIL PROTECTED] <[EMAIL PROTECTED]>, <[EMAIL
PROTECTED]>
com> cc:
Fax to:
05/26/2004 10:28 Subject: RE: slow insert into select
statement
AM
Hi,
I would start with finding out if it's the select or the insert that's
taking a long time...
Does the priceLast5 table have heavy indexes to build?
Try running the select seperately and see how long it takes...
Andrew
-----Original Message-----
From: Victor Pendleton [mailto:[EMAIL PROTECTED]
Sent: Wednesday 26 May 2004 14:13
To: 'nyem '; '[EMAIL PROTECTED] '
Subject: RE: slow insert into select statement
If you do the math a large result set will be created. You could
rewriting your query or adding more indexes to see if this speeds up the
process.
-----Original Message-----
From: nyem
To: [EMAIL PROTECTED]
Sent: 5/26/04 2:57 AM
Subject: slow insert into select statement
I have this insert statement that took a long time to execute:
INSERT INTO priceLast5
SELECT
DISTINCT a.commodity,
a.PricingDt
FROM rptPricingTEST a
WHERE
a.PricingDt = ( SELECT MAX(PricingDt)
FROM rptPricingTEST b
WHERE
b.PricingDt > @date1
AND b.PricingDt <= @date2
AND a.commodity = b.commodity
);
> Query OK, 3010 rows affected (4 min 17.37 sec)
Is there any way I can modify the sql statement to speed this up?
Here's the result of explain (splitted for easy reading):
id select_type table type possible_keys key
-- ------------------ ----- ----- ------------- ------------
1 PRIMARY a index NULL idxcommodity
2 DEPENDENT SUBQUERY b ref idxcommodity idxcommodity
id key_len ref rows Extra
-- ------- --------------------- ------ ------------------------
1 25 NULL 384155 Using where; Using index
2 22 dbsdvitmp.a.commodity 52 Using where; Using index
And here's the rest of the sql before the above insert:
SELECT @date1:=DATE_SUB( CURDATE(), INTERVAL 1 MONTH);
SELECT @date2:=CURDATE();
CREATE TEMPORARY TABLE priceLast5 (
commodity char(22) NOT NULL,
PricingDt date NOT NULL,
PRIMARY KEY (commodity),
KEY idxPricingDt (PricingDt),
);
LOCK TABLES rptPricingTEST a READ;
The source table rptPricingTEST consists of commodity id (which is a
concat of several ids), pricing date, and prices (low, hi, avg). I need
to generate a report of price change for all commodity for the date
interval selected by users. Since this will be a web-based report, to
wait for 4 minutes for a response is unacceptable. The actual table has
2 million rows which I guess can take forever to execute.
regards,
nyem
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql To unsubscribe:
http://lists.mysql.com/[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:
http://lists.mysql.com/[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
