Mod Name / Version: Search Accelerator v1.6
Version 1.6
-Fixes a bug in UBB 6.5 where maximum search daterange is ignored (thanks chuckee!)
Version 1.5
-Adds the ability to sort search results by relevance.
-Adds option to search parent posts only.
-Fixes bug in UBB 6.5 with searching for asterisks.
-Fixes bug in previous version of mod with phrase searches.
Version 1.0
-Updated to work with UBB 6.5's search result caching system.
Version 0.1
-Initial beta version for UBB 6.4.x.
Description: Anybody who runs a large board already knows what a huge toll user searches can take on your server. Not only can they take 10 seconds, 20 seconds or even longer to complete, but while they're in progress they lock the posts table making your forums unresponsive until the search is over. Even with the search result caching in 6.5, the underlying problem remains.
This mod allows the BB search script to take advantage of the build-in fulltext search capabilities of mySQL 4.0.1+. Searches performed on a small number of posts (due to date range and/or forum limits) will probably not be affected and may even be slower by a few milliseconds. However, complex searches performed on a large number of posts or using boolean expressions can be greatly accelerated. Below are a few informal benchmarks so you can get an idea of what kind of performance increase you might be able to expect.
Installation Notes: Our boards use a dedicated mySQL server with 3GB of RAM, your results may vary. Also, because the fulltext index requires a little more memory to maintain, this mod will slightly increase mySQL resource usage. The effect is relatively minor but if you have spent a lot of time tweaking your mySQL memory configuration you may need to readjust some values.
There are several notable differences between the BB's build-in search and mySQL's fulltext search capability. MySQL fulltext search can handle full boolean queries, making it more powerful than the stock BB search. Also, mySQL automatically excludes words that are 3 characters or less, or which appear in more than half of the rows. It also has a list of common stopwords which are automatically excluded, so a search for 'hello' will not return any results. These settings can all be changed on your server, for more information see
Full-Text Search Functions,
Boolean Full-Text Searches and
Fine-Tuning MySQL Full-Text Search in the mySQL manual.
I'm
extremely interested in any feedback or suggestions from people who have tried this mod. One thing which I'm considering for a future version is consolidating some of the search queries using UNION statements in order to eek out a bit more performance. While this should work in theory, so far my preliminary experiments actually showed a significant degradation in speed when this tactic is used. If anybody has more information on this I'd be interested.
Benchmarks: These benchmarks are from my live board. At the time they were taken we had about 300 active users, 128,635 topics and 1,283,932 replies. Note that I lowered the minimum length of indexed words to three characters on my server. If you leave yours at the default of four, you may see a slightly faster performance increase.
Search term: elephant
Settings: Search in subject and body, all forums
Search posts newer than 1 week.
Original: Generated in 0.561 seconds in which 0.556 seconds were spent on a total of 8 queries.
Modified: Generated in 0.065 seconds in which 0.056 seconds were spent on a total of 8 queries.
Search posts newer than 1 month.
Original: Generated in 2.336 seconds in which 2.323 seconds were spent on a total of 8 queries.
Modified: Generated in 0.064 seconds in which 0.052 seconds were spent on a total of 8 queries.
Search posts newer than 1 year.
Original: Generated in 8.063 seconds in which 8.038 seconds were spent on a total of 8 queries.
Modified: Generated in 0.079 seconds in which 0.059 seconds were spent on a total of 8 queries.
Search term: "web browser"
Settings: Search in subject and body, all forums
Search posts newer than 1 week.
Original: Generated in 0.53 seconds in which 0.526 seconds were spent on a total of 8 queries.
Modified: Generated in 0.178 seconds in which 0.172 seconds were spent on a total of 8 queries.
Search posts newer than 1 month.
Original: Generated in 2.309 seconds in which 2.303 seconds were spent on a total of 8 queries.
Modified: Generated in 0.178 seconds in which 0.17 seconds were spent on a total of 8 queries.
Search posts newer than 1 year.
Original: Generated in 12.042 seconds in which 11.988 seconds were spent on a total of 8 queries.
Modified: Generated in 0.189 seconds in which 0.176 seconds were spent on a total of 8 queries.
Working Under: UBB.Threads 6.5
Mod Status: Finished
Any pre-requisites: mySQL v4.0.1+
Author(s): Ythan
Date: 10/07/04
Files Altered: /dosearch.php
/languages/english/dosearch.php
/languages/english/search.php
/templates/default/dosearch.tmpl
/templates/default/search.tmpl
Database Altered: Yes, indexes created on w3t_Posts.