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

SQL query taking way too long, locking up other queries

    XMLWordPrintable

Details

    • Bug
    • Status: Closed (View Workflow)
    • Major
    • Resolution: Closed
    • 3.3.2
    • None
    • Extensions

    Description

      For some reason, we are seeing this query constantly show up in slow query logs and taking up 20-30 seconds to evaluate fully, and I somehow traced it to Smartfeed extension.

       

       

      SELECT f.*, t.*, p.*, u.*, 
      tt.mark_time AS topic_mark_time, 
      ft.mark_time AS forum_mark_time 
      FROM (phpbb_forums f CROSS JOIN phpbb_topics t CROSS JOIN phpbb_posts p CROSS JOIN phpbb_users u) 
      LEFT JOIN phpbb_topics_track tt ON (t.topic_id = tt.topic_id AND tt.user_id = u.user_id) 
      LEFT JOIN phpbb_forums_track ft ON (f.forum_id = ft.forum_id AND ft.user_id = u.user_id) 
      WHERE f.forum_id = t.forum_id 
      AND t.topic_id = p.topic_id 
      AND p.poster_id = u.user_id
      AND p.post_time > 1639128751
      AND p.forum_id IN (1, 9, 8, 6, 4, 14, 15, 18, 22, 24, 25, 23, 21, 28, 31, 32, 33, 34, 38, 39, 35, 40, 41, 44, 45, 49, 50, 7, 54, 56, 59, 61, 60, 65, 66, 67, 71, 69, 73, 78, 75, 79, 82, 81, 85, 87, 90, 94, 96)
      AND p.post_visibility = 1
      AND forum_password = ''
      AND topic_status <> 2 
      ORDER BY f.left_id, f.right_id, t.topic_last_post_time, p.post_time
      LIMIT 18446744073709551615

       

      If I run it manually in phpMyAdmin, it literally locks it up and I have to manually kill that process as it never finished evaluating in there.

       

      Is there any way this query can be optimized somehow?

       

      Attachments

        Activity

          People

            Unassigned Unassigned
            Crazy Serb Crazy Serb
            Votes:
            0 Vote for this issue
            Watchers:
            1 Start watching this issue

            Dates

              Created:
              Updated:
              Resolved: