What is more efficient, a where clause or a join with million plus row tables?

For modern RDBMS there is no difference between "explicit JOIN" and "JOIN-in-the-WHERE" (if all JOINS are INNER) regards performance and query plan.

The explicit JOIN syntax is clearer and less ambiguous (see links below)

Now, the JOIN-before-WHERE is logical processing not actual processing and the modern optimisers are clever enough to realise this.

Your problem here is most likely indexing.

Please show us all indexes and keys on these tables. And the query plans

Note: this question would have been close on StackOverflow for being a duplicate by now... COUNT(1) vs COUNT(*) is another busted myth too.

• https://stackoverflow.com/questions/5654278/sql-join-is-there-a-difference-between-using-on-or-where/5654338#5654338
• https://stackoverflow.com/questions/3003457/count-vs-countcolumn-name-which-is-more-correct/3003533#3003533
• https://stackoverflow.com/questions/1221559/count-vs-count1/1221649#1221649

You have to refactor the query altogether

Try performing the WHERE clauses earlier and the JOINs later

Select Count(1) from DetailsTable dt
join (Select UserId,Id FROM MasterTable where
created between @date1 and @date2) mt on mt.Id = dt.MasterId 
join (Select Id FROM UserTable WHERE Role is NULL) ut
on ut.Id = mt.UserId;

Even if you run an EXPLAIN plan on this refactored query and it looks worse that your original, try it anyway. The temp tables created internally will perform cartesian joins but the those tables are smaller to work with.

I got this idea from this YouTube video.

I tried out the principles from the video in a very complex question in StackOverflow and got a 200 point bounty.

@gbn mentioned making sure you have the right indexes in place. In this case, please index the created column in MasterTable.

Give it a Try !!!

UPDATE 2011-06-24 22:31 EDT

You should run these queries:

SELECT COUNT(1) AllRoles FROM UserTable;
SELECT COUNT(1) NullRoles FROM UserTable WHERE Role is NULL;

If NullRoles X 20 < AllRoles (in other words, if NullRoles is less then 5% of table rows), you should create a non-unique index the Role in UserTable. Otherwise, a full table of UserTable would suffice since the Query Optimizer may possibly rule out using an index.

UPDATE 2011-06-25 12:40 EDT

Since I am a MySQL DBA, my method of doing things requires not trusting the MySQL Query Optimizer through positive pessimism and being conservative. Thus, I'll try refactoring a query or creating necessary covering indexes to get ahead of the MySQL Query Optimizer's hidden bad habits. @gbn's answer seems more complete in that SQL Server may have more "soundness of mind" evaluating queries.