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

Performance: Unneeded GROUP BY in update_forum_tracking_info

    Details

    • 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:
      None
    • Environment:
      MySQL 5.1.54 using InnoDB

      Description

      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.

        Attachments

          Activity

            People

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

              Dates

              • Created:
                Updated:
                Resolved: