5 million posts. Wow.
We are running into problems too and are having to upgrade our hardware to accommodate our measly 1 million posts. In talking with Sirdude I know he has been pretty militant to optimize every query he can.
I think there are a couple of tables that could use indexes. Topic is sticky and topic is approved.
Sirdude has some code that runs the search through Apache Solr. Contact him and I could provide some help getting that setup with the Solr part. We don't have our forums on it yet but I am using our forum data in solr for something else that processes a hundred queries a minute. Full text ueries that will crash your server in MySQL will run in a millisecond in Solr. Solr's footprint is actually very low, however the import process seems to load the full query results into ram, so you'll need to make sure your start your servlet up with 5 gigs of ram, which doesn't sound like will be a problem with for you. I imagine there might be other ways to run the import with lower memory but I haven't investigated it.
Who are you hosting with?
