Slow Query

[JonnoTheDev]

good point

[JonnoTheDev]

This query is slightly faster

SELECT u.siteId FROM user u WHERE userId NOT IN(SELECT userId FROM submission WHERE bookmarkUrl ='http://www.xyz.com' AND scriptId=4) AND u.scriptId=4 GROUP BY u.siteId

[fenway]

Please post EXPLAIN output again.

[JonnoTheDev]

Attached

 

[attachment deleted by admin]

[fenway]

Again, drop the group by for a minute -- and dependent subquery is the problem.  Use a LEFT JOIN ... is NULL instead.

[JonnoTheDev]

Use a LEFT JOIN ... is NULL instead.

Sorry, lost me there a bit matey. Are you going back to the original query on the first page of this post.

[fenway]

Sorry, I thought it said correlated, not dependent -- though I do prefer the LEFT JOIN over NOT IN().  But no, not to the original query, just to the one you recently posted.

 

Add an index on ( scriptID, siteID ) and see how it performs -- drop the other one, of course.