CODEWITHSUNDEEP
mysqlperformancesubquery
MikeBopf
MikeBopf

Reputation: 50

Improve performance of subquery in MySQL

Here is my query which works using two subqueries and temporary tables:

select visit.pid, visit.pidvnum, visit.hpv16, visit.qc_hst
FROM visit,
    (select distinct x.pid  from
        (select pid from visit where visit.qc_hst = 1) x,
        (select pid from visit where visit.hpv16 = 1) y
    where x.pid = y.pid) as subtbl
where visit.pid = subtbl.pid
AND (visit.qc_hst = 1 OR visit.hpv16 =1);

Is there a way to rewrite this query to eliminate one or both of the subqueries? I've also used an IN clause with the same two subqueries, but that's even worse. I can't index the fields like qc_hst and hpv16 since there are dozens of them, and any of them could potentially be part of the query.

Here is a sqlfiddle link: http://sqlfiddle.com/#!2/68bd6/6/0

Thanks a lot - I'm really banging my head against the wall on this one...

Upvotes: 0

Views: 100

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269973

Your query appears to be looking for records where a pid has both those conditions being met. The following is an alternative approach:

select v.pid, v.pidvnum, v.hpv16, v.qc_hst
from visit v join
     (select v.pid
      from visit v
      where visit.qc_hst = 1 or visit.hpv16 = 1
      group by pid
      having SUM(v.qc_hst = 1) > 0 and sum(v.hpv16 = 1) > 0
     ) vp
     on v.pid = vp.pid
where visit.qc_hst = 1 or visit.hpv16 = 1

An index on visit(pid) will probably help any of the queries run faster.

Upvotes: 1

Related Questions