Re: Fulltext searching very slow
Oh and looking at a MySQL performance guide for text search, apparently bbPress’s search is using ALL of the slowdown triggers:
order by
group by
and worst of all SQL_CALC_FOUND_ROWS
How long does this query take without the modifiers:
SELECT * FROM bb_posts WHERE post_text LIKE '%test%' LIMIT 5;
because that’s as fast as it can possibly get without an index and should be used as a baseline.
Looking at the original query, I believe they are causing a FULL scan of the entire table to rank and group it first. That’s going to be insanely slow.
You might be able to just use LIKE with a simple sort by post date DESC.
The largest bbPress install I have access to has nearly 100,000 rows and still only takes 0.04 seconds for that trimmed query. The original queries you posted take 0.5 seconds so that’s 10x slower.
