RE: slow insert into select statement

[Andrew Braithwaite]

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]