How can I optimize this query?

Question

Hi,

select sum(viewcount)  from video where id in(
select videoid from syndicatedvideos where videoid in (select syndicatedvideoid from syndicatedvideos where videoid=31)
union
select syndicatedvideoid from syndicatedvideos where videoid in (select syndicatedvideoid from syndicatedvideos where videoid=31)) or id in (31).

How can I optimize the query above?

Answer 1

I think this should work.

select sum(viewcount)  from video 
where id in( select syndicatedvideoid from syndicatedvideos where videoid=31) or or id in (31)

Please check and verify. Thanks.

Answer 2

SELECT sum(video.viewcount) FROM video 
  LEFT JOIN syndicatedvideos sv1 ON video.id=sv1.videoid  
  LEFT JOIN syndicatedvideos sv2 ON video.id=sv1.syndicatedvideoid 
WHERE 
  sv1.videoid=31 OR
  sv2.videoid=31 OR
  video.id=31;

Answer 3

select sum(viewcount)
from video
where id in(

    select id from(
     select videoid as id
     from syndicatedvideos

     union

     select syndicatedvideoid as id
     from syndicatedvideos)

    where id in(
     select syndicatedvideoid
     from syndicatedvideos
     where videoid=31)

) or id in (31)

Answer 4

select sum(viewcount)  
from video v
where exists (
    select 1 
    from syndicatedvideos sv 
    where (
     v.id = sv.videoid
     or v.id = sv.syndicatevideoid )
    and exists (
     select 1 
     from syndicatedvideos sv2
     where sv.videoid = sv2.syndicatevideoid
     and sv2.videoid=31)
) 
or id = 31.

What I changed:

• replaced last in() with =
• replaced your main in() subquery with an exists which matches both on videoid and syndicatevideoid, thus avoiding union with a 2nd sub-sub-clause
• formatted nicely to aid in understanding :-)