In functions.php's update_forum_tracking_info(), the following query is executed to check if the forum has any unread topics left:

SELECT t.forum_id FROM ' . TOPICS_TABLE . ' t

LEFT JOIN ' . TOPICS_TRACK_TABLE . ' tt ON (tt.topic_id = t.topic_id AND tt.user_id = ' . $user->data['user_id'] . ')

WHERE t.forum_id = ' . $forum_id . '

	AND t.topic_last_post_time > ' . $mark_time_forum . '

	AND t.topic_moved_id = 0

	AND (tt.topic_id IS NULL OR tt.mark_time < t.topic_last_post_time)

GROUP BY t.forum_id

LIMIT 1

It seems to me that the GROUP BY clause at the end of this query is unnecessary. The execution time for this query on my server (MySQL 5.1.54 using InnoDB) with a particular set of bindings is about 0.95 seconds, which is pretty slow. This is comparable to the execution time with a COUNT and no LIMIT clause (touching 120,000 rows).

With the GROUP BY clause removed, execution time for this query is closer to 0.25 seconds.

In both cases, the execution plan is the same:

id,select_type,table,type,possible_keys,key,key_len,ref,rows,Extra

1,SIMPLE,t,range,"last_post_time,forum_appr_last,fid_time_moved",fid_time_moved,7,NULL,53504,"Using where; Using index"

1,SIMPLE,tt,eq_ref,"PRIMARY,topic_id",PRIMARY,6,"const,forum_main.t.topic_id",1,"Using where"

If I use "SHOW SESSION STATUS LIKE 'Handler_re%'" in order to check how many rows are read by the query, one original GROUP BY query makes 21,714 key reads, while the equivalent version without GROUP BY makes just 11 key reads.