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]
