Alter and Optimize sql query

Question

I need to please change this SQL query to NOT use sub-query with IN, I need for this query to work faster.

here is the query i am working on. About 7 million rows.

SELECT `MovieID`, COUNT(*) AS `Count`
FROM `download`
WHERE `UserID` IN (
SELECT `UserID` FROM `download`
   WHERE `MovieID` = 995
)
GROUP BY `MovieID`
ORDER BY `Count` DESC

Thanks

Answer 1

Something like this - but (in the event that you switch to an OUTER JOIN) make sure you're counting the right thing...

 SELECT MovieID
      , COUNT(*) ttl
   FROM download x
   JOIN download y
     ON y.userid = x.userid
    AND y.movieid = 995
  GROUP 
     BY x.MovieID
  ORDER  
     BY ttl DESC;