Got any indexes?
--ja
On Wed, 11 Feb 2004, nyem wrote:
> 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
>
>
>
>
>
--
