This query returns the number of threads per username, per title, per month, per forum within a specific timerange.
Since the limiting factors here are in the dbo.Threads table (namely CreatedOn and IsPublic), why didn't you start with this table in the FROM clause?
Then join dbo.Forums, dbo.Posts and dbo.Users as a consequence.
Another factor which niggles me is the function on month in the GROUP BY clause. An index on the PostDate could not be used. And it gives back a specific date to represent a month. Would a subquery replacing the dbo.Posts table help or hurt performance
e.g. INNER JOIN (select ThreadID, Poster, UserID, dateadd(month, datediff(month, 0, p.PostDate), 0) FROM dbo.Posts ) p ON on t.ThreadID = p.ThreadID ?
The solution that you arrived at is to add an index. Now, what if the tables in question already have many indexes? Adding more will make all write-operations slower and lead to more latch-operations and longer lock-times. Taking indexes away will make other queries slower (assuming that they are being used, of course). What strategies would you try in such a case?
Thanks for the article,