SQL COUNT(col) vs extra logging column... efficiency?

I can't seem to find much information about this.

I have a table to log users comments. I have another table to log likes / dislikes from other users for each comment. Therefore, when selecting this data to be displayed on a web page, there is a complex query requiring joins and subqueries to count all likes / dislikes. My example is a query someone kindly helped me with on here to achieve开发者_开发百科 the required results:

SELECT comments.comment_id, comments.descr, comments.created, usrs.usr_name, 
  (SELECT COUNT(*) FROM comment_likers WHERE comment_id=comments.comment_id AND liker=1)likes,
  (SELECT COUNT(*) FROM comment_likers WHERE comment_id=comments.comment_id AND liker=0)dislikes,
  comment_likers.liker
FROM comments
INNER JOIN usrs ON ( comments.usr_id = usrs.usr_id )
LEFT JOIN comment_likers  ON ( comments.comment_id = comment_likers.comment_id 
  AND comment_likers.usr_id = $usrID )
WHERE comments.topic_id=$tpcID
ORDER BY comments.created DESC;

However, if I added a likes and dislikes column to the COMMENTS table and created a trigger to automatically increment / decrement these columns as likes get inserted / deleted / updated to the LIKER table then the SELECT statement would be more simple and more efficient than it is now. I am asking, is it more efficient to have this complex query with the COUNTS or to have the extra columns and triggers?

And to generalise, is it more efficient to COUNT or to have an extra column for counting when being queried on a regular basis?

Your query is very inefficient. You can easily eliminate those sub queries, which will dramatically increase performance:

Your two sub queries can be replaced by simply:

sum(liker) likes,
sum(abs(liker - 1)) dislikes,

Making the whole query this:

SELECT comments.comment_id, comments.descr, comments.created, usrs.usr_name, 
    sum(liker) likes,
    sum(abs(liker - 1)) dislikes,
    comment_likers.liker
FROM comments
INNER JOIN usrs ON comments.usr_id = usrs.usr_id
LEFT JOIN comment_likers  ON comments.comment_id = comment_likers.comment_id 
  AND comment_likers.usr_id = $usrID
WHERE comments.topic_id=$tpcID
ORDER BY comments.created DESC;