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

Performance: Unneeded GROUP BY in update_forum_tracking_info

    XMLWordPrintable

Details

    • Improvement
    • Status: Closed (View Workflow)
    • Minor
    • Resolution: Fixed
    • 3.0.10
    • 3.0.11-RC1
    • Viewing forums
    • None
    • 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

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

            Dates

              Created:
              Updated:
              Resolved: