When to add an extra "helper" table rather than running complex query I'm trying to build a "notification" feature into my site similar to Facebook:

I would show a little graphic with a number indicating the number of new comments to nodes a user created or commented on. 

The query will probably look like this, assuming uid=1 (haven't tested yet):

SELECT count(node.nid) as new_comments
FROM {node} node
INNER JOIN {node_comment_statistics} ncs ON node.nid = ncs.nid
INNER JOIN {history} h ON h.nid = node.nid
WHERE ( (h.uid = 1) AND (h.timestamp &lt; ncs.last_comment_timestamp)
AND ((node.uid = 1) OR (0 &lt; (SELECT COUNT(*) FROM comment c WHERE c.uid = 1 AND c.nid = node.nid)) )))


This query would run with every page request for all logged in users which could impact performance. 
I could create a "helper" table where I keep a "new_comments" counter for each uid. For every comment added or updated I would then check which users need their "new_comments" counter incremented.

My question:

Is there a rule of thumb when to use such a "helper" table instead of running complex queries. Or do I need to create say a million records in Devel and test the performance of above query under full load?
As long as all your fields used in JOIN and WHERE are indexed properly, you don't really need a separate table to do this, I probably would rewrite it in a single query, sub queries tend to be slow.

If you want something intermediate (in case you have > 1.000.000 rows), you have 2 options:


Create a temporary table and keep it up to date using cron and/or
hooks 
Use caching in Drupal to remember the result and combine it
with hooks


Both solution have pro's and con's:


Can be updated by cron, but has a record for every user, no matter if that user visits the site or not.
Needs to run the query when there's no cached data, but will be faster on a second page visit.