[Erwin]

Originally Posted by Freddie Bingham

Here is one optimization. As I have time to work on this for 3.1, I'll try to post changes for you.

With 3.0.2 or 3.0.3 you should edit search.php and look for this around line 1303:

FROM " . TABLE_PREFIX . "post AS post

Change this to:

FROM " . TABLE_PREFIX . "post AS post " . iif($vboptions['fulltextsearch'] AND $searchuser, "USE INDEX (userid)") . "

When searching by posts by a specific user and returning results as posts, this will force mysql to search based on userid rather than using the fulltext index. On the whole this will be faster than searching fulltext and then manually scanning for userids.

I'm just getting into imposing the proper limit options and re-evaluating the relevancy junk for the non-fulltext search. It is the extra queries that we have in place to support this pseudo-relevancy that complicates limiting searches when returning results as threads.

FWIW, I've found removing the relevance checks speeds up the search, and the quality of the search is not really affected.

Also, like you say, it's really the returning the results as threads that's the site killer for large forums even with optimization and fulltext. Returning results as posts is not an issue with the right search code.