CODEWITHSUNDEEP
mysql
Ankur Dhanuka
Ankur Dhanuka

Reputation: 1237

Alter and Optimize sql query

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

Upvotes: 6

Views: 233

Answers (3)

Strawberry
Strawberry

Reputation: 33935

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;

Upvotes: 7

CloudyMarble
CloudyMarble

Reputation: 37566

Use Exists instead, see Optimizing Subqueries with EXISTS Strategy:

Consider the following subquery comparison:

outer_expr IN (SELECT inner_expr FROM ... WHERE subquery_where) MySQL evaluates queries “from outside to inside.” That is, it first obtains the value of the outer expression outer_expr, and then runs the subquery and captures the rows that it produces.

A very useful optimization is to “inform” the subquery that the only rows of interest are those where the inner expression inner_expr is equal to outer_expr. This is done by pushing down an appropriate equality into the subquery's WHERE clause. That is, the comparison is converted to this:

EXISTS (SELECT 1 FROM ... WHERE subquery_where AND outer_expr=inner_expr) After the conversion, MySQL can use the pushed-down equality to limit the number of rows that it must examine when evaluating the subquery.

Upvotes: 0

naveen goyal
naveen goyal

Reputation: 4629

filter direct on movieId..you does not need to add sub query. it can be done by using movieID =995 in where clause.

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

Upvotes: -2

Related Questions