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

Question

We run a website that has 250MM rows in one table and in another table that we join it to for most queries has just under 15MM rows.

Sample structures:

MasterTable (Id, UserId, Created, Updated...) -- 15MM Rows
DetailsTable (Id, MasterId, SomeColumn...) -- 250MM Rows
UserTable (Id, Role, Created, UserName...) -- 12K Rows

We regularly have to do a few queries against all these tables. One is grabbing statistics for free users (~10k free users).

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

Problem is this query will some times run a long damn time due to the fact that the joins happens long before the where.

In this case would it be wiser to use wheres instead of joins or possibly where column in(...)?

Answer 1

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

Answer 2

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.

Answer 3

We had a [Detail] table about 75M rows; a [Master] table about 400K rows and a related [Item] table which had 7 rows-always and forever. It stored the small set of “Item numbers” (1-7) and was modeling a paper form, millions of which were printed and distributed every month. The fastest query was the one you’d least likely think about first, involving the use of a Cartesian Join. IIRC, it was something like:

SELECT m.order_id, i.line_nr, d.Item_amt
FROM Master m, Item i 
INNER JOIN Detail d ON m.order_id = d.order_id

Even though there is a logical “id” link between [Item] and [Detail] the CROSS JOIN worked better than INNER JOIN.

The RDBMS was Teradata with its MPP technology, and IDR what the indexing scheme was. The 7 row table had no index as TABLE SCAN always performed the best.