performance - Mysql query very slow and not using proper index (By using group by, IN operator ) -

below query taking 5+ sec time execute ( table contains 1m+ records ). outer query not using proper index fetching data using full table scan.can me how optimize it..

query

select x    usercardxref x   x.usercardxrefid in(     select max(y.usercardxrefid)        usercardxref y       y.usrid in(1001,1002)       group          y.usrid      having count(*) > 0     ) 

query explain

query statistics

execution plan

i re-write query

select x.* usercardxref x join (  select max(usercardxrefid),usrid usercardxref   usrid in (1001,1002) group usrid )y on x.usercardxrefid = y.usercardxrefid 

the indexes need as

alter table usercardxref add index usercardxrefid_idx(usercardxrefid) 

usrid indexed per explain plan no need add that

also have having count(*)>0 using max() function , never have 0 rows given group have removed that.