List:General Discussion« Previous MessageNext Message »
From:<jabbott Date:February 11 2004 7:21am
Subject:Re: sloooow sql query
View as plain text  
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
> 
> 
> 
> 
> 

-- 

Thread
sloooow sql querynyem11 Feb
  • Re: sloooow sql queryjabbott11 Feb
  • Re: sloooow sql queryEd Leafe11 Feb