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.