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(...)?

  • 1
    What database and version? – Leigh Riffel Jun 24 '11 at 15:35
  • 2
    have you tried both ways? – gbn Jun 24 '11 at 15:43
  • If this were Oracle I would create a function based index for the UserTable on NVL2(Role,NULL,ID), but this looks like another DB. – Leigh Riffel Jun 24 '11 at 15:58

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.

| improve this answer | |
  • 2
    It is NOT ALWAYS TRUE that there is no difference between join and where clause. I optimize the long running queries all the time and sometimes the queries using where clause perform better than those using join by a factor of up to 70x. If it was that simple and straightforward, life would be all rainbows and unicorns. And this is not about some ancient obscure engine - right now I am looking at 70x advantage of where clause in SQL 2012. – ajeh Feb 11 '15 at 20:05
  • Even further, I often observe the exact same plans from both approaches and isolated the queries perform exactly the same, but when the where clause query runs within the large batch it is supposed to be a part of, it outperforms the join query by a huge margin. The SQL queries do not execute in vacuum - they are affected by the rest of the server payload, and often the where clause queries fare quite well, which is an annoyance as join syntax is indeed much cleaner. – ajeh Feb 11 '15 at 20:32
  • 3
    @ajeh: I would suggest that your experience is very untypical. You have bigger issues with queries if you have x70 differences: it is that simple – gbn Feb 12 '15 at 8:39

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.

| improve this answer | |

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.

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.