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

Search times out on a medium size forum

    XMLWordPrintable

Details

    • Improvement
    • Status: Open (View Workflow)
    • Minor
    • Resolution: Unresolved
    • 3.3.0
    • None
    • Search

    Description

      Search times out on a medium size forum when searching by multiple words. Search "index" is kept in the mysql tables. The following search mysql query runs multiple seconds eventually timing out the web request:

       

      SELECT SQL_CALC_FOUND_ROWS p.post_id FROM (bbs_search_wordmatch m1 CROSS JOIN bbs_search_wordmatch m2 CROSS JOIN bbs_search_wordmatch m0) LEFT JOIN bbs_posts p ON (m0.post_id = p.post_id) WHERE m0.word_id = 8113 AND m1.word_id = 8114 AND m1.post_id = m0.post_id AND m2.word_id = 4366 AND m2.post_id = m0.post_id AND ((p.forum_id <> ...
      AND p.post_visibility = 1) OR p.forum_id IN (...)) AND p.forum_id <> ... GROUP BY p.post_id, p.post_time ORDER BY p.post_time DESC
      LIMIT 250;

       

      As we see there is a (multiple) Cartesian products here which is not super efficient when it comes to larger tables. I suggest to consider replacing this query with the one along the lines of:

      select p.post_id from (select post_id, count(distinct word_id) from bbs_search_wordmatch where word_id in (8113, 8114, 4366) group by 1 having count(distinct word_id) = 3) t join bbs_posts p on p.post_id = t.post_id where ((p.forum_id <> ... AND p.post_visibility = 1) OR p.forum_id IN (...)) AND p.forum_id <> ... GROUP BY p.post_id, p.post_time ORDER BY p.post_time DESC LIMIT 250;

       

      The query produced the same result, but takes 20-30 milliseconds instead of 12-15 seconds. Idea behind it is to search for the posts that have all three words (having keyword). I assume it could be just a specific scenario in search function, but probably could provide an option to eliminate cross join usage.

       

      Thank You.

      Attachments

        Activity

          People

            Unassigned Unassigned
            UNRULY UNRULY [X] (Inactive)
            Votes:
            0 Vote for this issue
            Watchers:
            2 Start watching this issue

            Dates

              Created:
              Updated: