Contest Entry:

For query 1

I would put an index on Time and Data for the analysis log table.

For query 2

The query 1 item would take care of this query as well.

For query 1 and 2. The interesting thing would be to cache the days counts off in server memory or another temporary table then do a nightly or hourly job to write all that down to the databaase or copy it to the analysis log. One other arc change I would make is down use a varchar(50) if possible, can these data events be a key of an int or bigint. That would far improve the indexing. Maybe provide a lookup table to find the description.

For query 3 remove the order by if possible, the sort might be better selecting out of the temp table later if needed. So, add a PostDate column there. Alternately, add an index on cs_Posts with a ThreadID,IsApproved,PostDate,PostID,PostLevel,ApplicationPostType (though this is wide).

Query 4:

Looks like an index is needed on cs_weblog_weblogs on sectionID, but selectivity might just be poor. I would alias applicationtype in the where clause, also is W.* really needed? Expand the W.* items out. Write the , out as an ANSI join style just for best practice.