Uploaded image for project: 'phpBB3'
  1. phpBB3
  2. PHPBB3-10565

Performance: Unneeded GROUP BY in update_forum_tracking_info


    • Type: Improvement
    • Status: Closed
    • Priority: Minor
    • Resolution: Fixed
    • Affects Version/s: 3.0.10
    • Fix Version/s: 3.0.11-RC1
    • Component/s: Viewing forums
    • Labels:
    • Environment:
      MySQL 5.1.54 using InnoDB


      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:

      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.




            • Assignee:
              bantu Andreas Fischer
              thenickdude thenickdude [X] (Inactive)
            • Votes:
              0 Vote for this issue
              1 Start watching this issue


              • Created: