mysql - How efficiently check record exist more than 2 times in table using sub-query? -

i have query . have compound index cc.key1,cc.key2. executing in big database

select * cc    (  (     (select count(*) service s              cc.key1=s.sr2 , cc.key2=s.sr1) > 2      ,      cc.key3='new'     )   or      (     (select count(*) service s              cc.key1=s.sr2 , cc.key2=s.sr1) <= 2     )      ) limit 10000; 

i tried make inner join , getting slower . how can optimize query ?

the trick here being able articulate query problem:

select * cc t1 inner join (     select cc.key1, cc.key2     cc cc     left join service s         on cc.key1 = s.sr2 ,            cc.key2 = s.sr1     group cc.key1, cc.key2     having count(*) <= 2 or            sum(case when cc.key = 'new' 1 else 0 end) > 2 ) t2     on t1.key1 = t2.key1 ,        t1.key2 = t2.key2 

explanation:

your original 2 subqueries add count if given record in cc, given key1 , key2 value, matched corresponding record in service table. strategy behind inner query use group by count number of times happens, , use instead of subqueries. first count condition bottom subquery, , second 1 top.

the inner query finds key1, key2 pairs in cc corresponding records should retained. , recognize these 2 columns criteria in original query determining whether record cc gets retained. then, inner query can inner joined cc again final result set.

in terms of performance, answer leave desired, should better massive correlated subquery, had.