Uploaded image for project: 'phpBB'
  1. phpBB
  2. PHPBB-10894

'View unanswered posts' doesn't use a database index

XMLWordPrintable

    • Icon: Bug Bug
    • Resolution: Won't Fix
    • Icon: Major Major
    • 3.0.15-RC1
    • 3.0.10
    • Search
    • None
    • phpBB 3.0.10
      MySQL(i) 5.1.41-3ubuntu12.10-log
      PHP 5.3.2-1ubuntu4.15
      Ubuntu Server 12.04

      Clicking on the "View unanswered posts" link on the board index page generates a database query that can't use any indexes, requiring a full table scan of phpbb_topics.

      The code is in search.php line 385. The query looks like this:

      mysql> EXPLAIN SELECT DISTINCT t.topic_last_post_time, p.topic_id FROM phpbb_posts p, phpbb_topics t WHERE t.topic_replies = 0 AND t.topic_moved_id = 0 AND p.topic_id = t.topic_id AND p.post_approved = 1 AND p.forum_id NOT IN (17, 22, 28, 29) ORDER BY t.topic_last_post_time DESC LIMIT 1001 \G

      1. row
      id: 1
      select_type: SIMPLE
      table: t
      type: ALL
      possible_keys: PRIMARY
      key: NULL
      key_len: NULL
      ref: NULL
      rows: 154369
      Extra: Using where; Using temporary; Using filesort

      2. row
      id: 1
      select_type: SIMPLE
      table: p
      type: ref
      possible_keys: forum_id,topic_id,post_approved,tid_post_time
      key: topic_id
      key_len: 3
      ref: forum.t.topic_id
      rows: 49
      Extra: Using where

      2 rows in set (0.00 sec)

      On my forum, this isn't called very often, but can take 30 seconds or more when it is.

      FYI, the "Tapatalk" mobile plugin copies this code, so also generates these slow queries.

            CHItA CHItA
            parsim parsim [X] (Inactive)
            Votes:
            0 Vote for this issue
            Watchers:
            0 Start watching this issue

              Created:
              Updated:
              Resolved: