slow insert into select statement

nyem Wed, 26 May 2004 00:59:25 -0700

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]

slow insert into select statement

Reply via email to