I have something like this:
WHERE CompanyId not in
WHERE (IsPublic = 0) and CompanyId NOT IN
ShoppingLike ON Company.CompanyId = ShoppingLike.UserId
WHERE (ShoppingLike.IsWaiting = 0) AND
(ShoppingLike.ShoppingScoreTypeId = 2) AND
(ShoppingLike.UserId = 75)
My experiences are from Oracle. There is never a correct answer to optimising tricky queries, it's a collaboration between you and the optimiser. You need to check explain plans and sometimes traces, often at each stage of writing the query, to find out what the optimiser in thinking. Having said that:
SELECTby putting the entire contents of it's subquery
WHEREclause in a
). On the face of it will prevent that outer full scan of Company (or it's index of CompanyId). Try it, check the output is the same and get timings, then remove it temporarily before trying the below. The
NOT()may well cause the optimiser to stop considering an ANTI-JOIN against the ShoppingLike subquery due to an implicit OR being created.
NOT NULLcolumns. Without this (or the likes of an explicit
CompanyId IS NOT NULL) then ANTI-JOIN options are often discarded.
INNER JOINas you want ShoppingLike scanned first as it has all the filters against it. It wont make any difference but it reads easier and makes it possible to use a hint to scan tables in the order specified.
NOT INwhen sometimes the very similar
NOT EXISTSgives the optimiser more/alternative options.
All the above is just trial and error unless you start trying the explain plan. Oracle can, with a following wind, convert between
LEFT JOIN and
SELECT. 1M+ rows will create time to invest.