Geek Answers Handbook

Mysql help needed to optimize a subquery group

I seem to be a little stuck. This is a fairly direct request.

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

I'm not sure how to optimize it. Any help is appreciated. Basically I want to show some returns. So where the 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 explanation results are attached as an image.

enter image description here

+8
performance mysql group-by subquery
source share
3 answers

I guess this is more of a rewrite rather than an optimization, but in any case, I would try:

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

IN , as you used, 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 )
+2
source share

I think your request does not answer the question. As I understand it, you should first get all errors that have more than one associated identifier (a row in the table). And then get all these lines (and not just faultId).

Try the following:

 select * from faultrefunds_v2 where faultId in ( select faultId from faultrefunds_v2 group by faultId having count(*) > 1 )
+1
source share

More articles:


All Articles