slow insert into select statement

[nyem]

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]