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

Search times out on a medium size forum

    Details

    • Type: Improvement
    • Status: Open (View Workflow)
    • Priority: Minor
    • Resolution: Unresolved
    • Affects Version/s: 3.3.0
    • Fix Version/s: None
    • Component/s: Search
    • Labels:

      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

            • Assignee:
              Unassigned
              Reporter:
              UNRULY UNRULY
            • Votes:
              0 Vote for this issue
              Watchers:
              2 Start watching this issue

              Dates

              • Created:
                Updated: