How to optimize this query?

Question

Hello i have two tables:

**prices**
id
id_stock
price
date

**stocks**
id
stock_name
active

now i have to set stocks.active=0 for each stocks that has the MAX(prices.date) > 15 days (of a date i have to pass)

This is my query, but it's very very slow!!!

update stocks set stocks.active=0 where stocks.id IN (

SELECT prices.id_stock 
FROM prices
GROUP BY prices.id_stock
HAVING datediff('2010-08-17', MAX( prices.date )) > 15

)

How to optimize it?

Thank you really much!

Answer 1

UPDATE  stocks
SET     active = 0
WHERE   DATEDIFF('2010-08-17',
        (
        SELECT  MAX(prices.date)
        FROM    prices
        WHERE   id_stock = stocks.id
        )) > 15

Create an index on prices (id_stock, date)

Answer 2

I sometimes find rewriting the IN clause as an EXISTS improves matters...

UPDATE stocks SET stocks.active=0
    WHERE EXISTS (SELECT prices.id_stock 
                      FROM prices
                      WHERE prices.id_stock = stocks.id
                      GROUP BY prices.id_stock
                      HAVING datediff('2010-08-17', MAX( prices.date )) > 15);

Answer 3

Update stocks
Set active = 0
Where Exists    (
                Select 1
                From prices
                Where prices.id_stock = stocks.Id
                Having Max(prices.date) < DateAdd( '2010-08-17', Interval -15 Day)
                )