How to optimize this query?

Question

prices   I would id_stock price date

**stocks**
id
stock_name
active

now I need to set stocks.active=0for every stock that has MAX(prices.date) > 15 days(a date that I have to go through)

This is my request, but it is 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 very 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 sentences INas EXISTSimproving questions ...

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)
                )