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

Question

Below query was taking 5+ sec time to execute ( Table contains 1m+ records ). Outer query was not using proper index it always fetching data by using FULL table scan.can someone help me how to optimize it..

Query

SELECT x 
  FROM UserCardXref x 
 WHERE x.userCardXrefId IN(
    SELECT MAX(y.userCardXrefId) 
      FROM UserCardXref y 
     WHERE y.usrId IN(1001,1002) 
     GROUP 
        BY y.usrId 
    HAVING COUNT(*) > 0
    )

Query Explain

Query Statistics

Execution Plan

Answer 1

I would re-write the query as

select x.* from UserCardXref x
join (
 select max(userCardXrefId),usrId from UserCardXref 
 where usrId in (1001,1002) group by usrId
)y on x.userCardXrefId = y.userCardXrefId

The indexes you will need as

alter table UserCardXref add index userCardXrefId_idx(userCardXrefId)

usrId is already indexed as per the explain plan so no need to add that

Also you have having count(*)>0 you are already using max() function and it would never have 0 rows for a given group so I have removed that.