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