sloooow sql query

[nyem]

Hello,

I have this table which stores an ever changing price of items.

CREATE TABLE shop (
  article       INT(4) UNSIGNED ZEROFILL NOT NULL default '0000',
  date  DATE NOT NULL default '0000-00-00',
  price         DOUBLE(16,2) NOT NULL default '0.00',
  KEY article (article,date)
);
And I used this query to get the percentage change in price of the items:

SELECT
        s1.article,
        s2.date as prevDate, s2.price as prevPrice,
        s1.date as lastDate, s1.price as lastPrice,
        ROUND(((s1.price-s2.price)/s2.price)*100, 1) as percentChange
FROM shop s1, shop s2
WHERE
        s1.date=(
                SELECT MAX(s3.date) FROM shop s3
                WHERE s1.article=s3.article
                GROUP BY article
        ) AND
        s2.date=(
                SELECT MAX(s4.date) FROM shop s4
                WHERE s1.article=s4.article AND s4.date<s1.date
                GROUP BY article
        )
ORDER BY percentChange DESC;
It works fine on small number of rows, but when the table reaches 400 rows 
the time it took to execute the query was 16 sec. And my cpu shot up to 
100% whenever I populate 1000 rows. What have I done wrong here?

regards,
nyem



-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:    http://lists.mysql.com/[EMAIL PROTECTED]