subquery - Very Slow MYSQL Sub Query -

guys, im more of mssql guy im working on mysql right now.

iv written simple query, subquery , cant understand life of me why slow.

this query:

   select max(timestamp), user, status checkin room_id = 'room name' , timestamp        > date_sub(now() ,interval 4005 second) group user 

runs in 0.0034 seconds

yet relatively similiar query nested, takes on 6 seconds ..

select user, status checkin timestamp in (select max(timestamp) checkin room_id = 'room name' , timestamp > date_sub(now() ,interval 4005 second) group user) 

can please help? im stuck.

the table "checkin" has 900 rows in it. room_id column indexed.

cheers

edit guys .. heres result of explain

dependent subquery checkin ref room_id room_id 202 const 1104 using where; using temporary; using filesort

look using having clause achieve same results. mysql notoriously bad @ sub-query optimization, try this:

select max(timestamp) ts, user, status  checkin room_id = 'room name'  ,   timestamp > date_sub(now() ,interval 4005 second) group user having timestamp = ts 

also make sure there index on timestamp

alternatively:

select user, status  checkin room_id = 'room name'  ,   timestamp > date_sub(now() ,interval 4005 second) , not exists (select * checkin newer                  newer.timestamp>checkin.timestamp                 , newer.room_id = 'room name'                 , newer.user = checkin.user) group user