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 < ncs.last_comment_timestamp)
AND ((node.uid = 1) OR (0 < (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:

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

Both solution have pro's and con's:

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

If you want to do anything complex, that doesn't change often, or you know / can act on when it does change, you should use the "helper" table called cache - or just create your own cache_module_name table.

A single query like what you have posted above, is probably not going to be a big deal. But if you plan on have a lot of these kind of statistics, they quickly add up, instead you can cache the rendered output and improve performance a lot.

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.