Re: Fulltext searching very slow
Remember, the problem with MATCH AGAINST is it will not do partial words or words under 4 characters.
It’s not too hard to replace the search, you just have to decide which way you want to go. The regular expression will at least do 3 character words which I find is more common than you’d imagine.
The problem is that the time for any way without an index is going to increase dramatically once you start adding sorting and other options that cause full table scans. You can see this happen if you try to add a simple option to the regex demo like sorting by reverse post_id (which is a trick that should be a little faster than sorting by date).
SELECT * FROM bb_posts WHERE post_text LIKE '%test%' LIMIT 5 ORDER BY post_id DESC;
You might want to test a worse case scenario by using three character nonsense words that will cause a cache-miss like “zzz” and “aaa”. Change them each time so mysql cannot cache the results and give you faster times.
If the above example returns in an acceptable amount of time you can just replace bbpress’s built in search with that simple method. By parsing a query you can also AND words together instead of bbPress’s default OR which to me is incredibly annoying and useless (you’ll notice no major search engine like Yahoo or Google does OR by default).