Re: Fulltext searching very slow
Off the top of my head this looks like a very bad idea performance-wise:
AND NOT p.topic_id IN (SELECT t.topic_id FROM bb_topics
AS t WHERE t.topic_status <> '0')
Though mysql may optimize it on the fly. Technically there could be thousands of deleted topics on an active site which makes quite a few items to search for (though in your case, there are NO deleted topics, yet, so this won’t be problem early on). However I think topic_status has an index? If not, it’s even worse.
Alternatively you could “over-search” the results (ie. ask for 100 instead of 30) and reverse the check of the 100 topic_id’s to make sure topic_status=0.
Since you’d end up using two queries, you could merge the two needs, to check titles AND to check topic status for the previously returned list of posts.
You’re dead right about pagination becoming insanely complicated in this case. This could be solved by limiting result lengths to just 100 items max, and then use my “over-query” idea to ask for 200 just incase it hits alot of deletions.