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 > > > > > -- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
