Skip to:
Content
Pages
Categories
Search
Top
Bottom

Re: Fulltext searching very slow


Mark Barnes
Member

@mark8barnes

I tested this on a very large database under very low load. The standard bbpress search creates two queries, namely:

SELECT p.*, 0 AS search_score, MAX(post_time) AS post_time FROM bb_posts AS p JOIN bb_topics as t ON ( t.topic_id = p.topic_id ) WHERE p.post_text LIKE '%test%' AND p.post_status = '0' AND t.topic_status = '0' GROUP BY t.topic_id ORDER BY p.post_time DESC LIMIT 5;

and

SELECT t.*, MIN(p.post_id) as post_id, GROUP_CONCAT(p.post_text SEPARATOR ' ') AS post_text, 0 AS search_score FROM bb_topics AS t JOIN bb_posts as p ON ( t.topic_id = p.topic_id ) WHERE t.topic_status = '0' AND p.post_status = '0' AND ( (t.topic_title LIKE '%test%') OR p.post_text LIKE '%test%' ) GROUP BY t.topic_id ORDER BY t.topic_time DESC LIMIT 30

I ran both queries five times with different search terms. On my very large database, with no optimisation, the first query took (167s, 104s, 36s, 29.7s, 24s), the second (33s, 21s, 22s, 20s, 21s).

Skip to toolbar