Re: Fulltext searching very slow
It seems to me that the most important thing that you can do very easily is to actually utilise the full-text index by using MATCH AGAINST rather than LIKE %…%. That’s an easy win. This also makes relevancy searching possible.
The next thing would be to ensure that post_status is updated when topic_status is changed. That way, you could ignore topic_status when searching posts, and lose the join.