Optimize group by sub query in Mysql

Question

It's quite a straight-forward query.

If I run the queries separately it is not that slow but when I combine them its very slow.

I'm not sure how to optimise it. I'm basically only wanting to show multiple refunds. So where faultid exists more than once.

SELECT 
    r.* 
FROM 
    faultrefunds_v2 r
WHERE 
    r.id IN (SELECT r1.id 
             FROM faultrefunds_v2 r1 
             GROUP BY faultid
             HAVING count(r1.faultid) > 1);

The results from explain are have been attached as an image

Answer 1

IN clause the way you used it would be very slow, use JOIN instead:

SELECT r.* FROM ( 
  SELECT r1.id AS id
  FROM faultrefunds_v2 r1 
  GROUP BY faultid
  HAVING count(r1.faultid) > 1
) AS ids
LEFT JOIN faultrefunds_v2 AS r
ON( ids.id = r.id )

Answer 2

I guess, this qualifies rather as a re-writing than as an optimisation, but this is what I would try instead, anyway:

SELECT 
  r.* 
FROM faultrefunds_v2 r
WHERE EXISTS (
  SELECT *
  FROM faultrefunds_v2 r1 
  WHERE r1.faultid = r.faultid
    AND r1.id <> r.id
);