mysql subquery too slow

[S C]

Hi All,

I'm trying to write a query which at the moment is just too slow to be of any use. Have tried it enough times to now try to seek help.

First:
TABLE Stack
trade_num, trade_status, quantity, date_appeared, status

so every new trade that comes in has a trade num, it has 4 trade_status's and date_appearedis the trade date and the status is the status relative to the first day of entry.

So trade on 10/28 might look like this
112323 BOOKED 100 2005-10-28 NEW

THEN ON say 10/29, if quantity changes
112323 BOOKED 200 2005-10-29 MODIFIED

THEN ON say 10/30 if trade is processed
112323 BOOKED 200 2005-10-30 PROCESSED

So then I have three entries for the same trade id.

Now on 10/30 if I want to see all PROCESSED trades I do something like
"select * from stack where date_appeared=2005-10-30 where status=PROCESSED"

I'm trying to report all trades that have skipped the MODIFIED state, they just jump from NEW to PROCESSED

SO THE query I want to write should

look at PROCESSED loans TODAY, look at the last date this loan appeared (in above example 10/29) and see if its status is not MODIFIED, if so then report should have this trade in it.

I've been doing something like this so far

SELECT * FROM
(SELECT * FROM stack WHERE date_appeared='2005-10-30' AND status='PROCESSED')
AS T,
(SELECT A.* FROM stack As A,
(SELECT trade_num, MAX(date_appeared) As LastDate FROM stack WHERE date_appeared<'2005-9-29' GROUP BY trade_num) AS B
WHERE A.trade_num=B.trade_num AND A.date_appeared=LastDate AND status <> 'MODIFIED' )
As K
WHERE T.trade_num=K.trade_num

This is very very slow, so i'm looking for a better way to write this. The stack has about 850,000 records at the moment.

Thanks for your help

S.