Re: Fulltext searching very slow
I’m curious about this issue as performance problems always intrigue me. You must be using 0.9 as 1.0 has an index on stickies by default.
As far as fulltext search there is a trick I use because of the multiple issues with fulltext (not only speed but fulltext can’t do words less than 4 characters until you customize and rebuild mysql). The trick is to use regex and do a two pass query where you first exclude all the posts without the words and then allow mysql to do a regular scan of the remaining posts.
Query example from my Super-Search plugin:
WHERE post_text LIKE '%".$term."%'" AND post_text REGEXP ':<:".$term.":>:[^']' "
Compare the performance of that against a fulltext search that uses "MATCH post_text AGAINST $term" I don’t have enough data to do a huge benchmark but some simple tests with the cache off shows 0.4 seconds for the trick and 0.9 for the fulltext.
The only downside to the trick is you cannot do partial word searches that way. ie. $term="cat" will only return posts with the exact word “cat” and not “cats” or “category”. But it should be way faster.
Searching a huge number of posts is a non-trivial problem. It’s been known to crush other forums like vbulletin which has fancy code to prevent users from searching too often/too quickly and even disable search temporarily on high server load. Sites like Wikipedia have to go through several technically complex tricks to keep the search fast on that much data.
Many large sites end up using sphinxsearch to replace fulltext search. You could interface it to bbPress via their PHP api.
You can read more workarounds on the mysql fulltext search page (with far more knowledgeable people than me) http://dev.mysql.com/doc/refman/5.0/en/fulltext-search.html
