Mod Name / Version: Search Accelerator v0.1
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. This will be partially addressed in 6.5 with search result caching, but the underlying problem will remain.
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.
You should be aware this this mod is very much beta, thrown together quickly for internal use and not extensively tested by any means. I'm
extremely interested in any feedback or suggestions. One thing which I'm considering for a future version of this mod 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. It typically has about 125 users active at a time and at the moment there are 112,928 topics and 970,081 replies.
Search term: random
Settings: Search in subject and body, all forums
Search posts newer than 1 week.
Original: Generated in 0.968 seconds in which 0.102 seconds were spent on a total of 4 queries.
Modified: Generated in 0.718 seconds in which 0.186 seconds were spent on a total of 4 queries.
Search posts newer than 1 month.
Original: Generated in 1.041 seconds in which 0.1 seconds were spent on a total of 4 queries.
Modified: Generated in 0.743 seconds in which 0.157 seconds were spent on a total of 4 queries.
Search posts newer than 1 year.
Original: Generated in 10.978 seconds in which 10.604 seconds were spent on a total of 4 queries.
Modified: Generated in 0.748 seconds in which 0.142 seconds were spent on a total of 4 queries.
Search term: "random phrase search"
Settings: Search in subject and body, all forums
Search posts newer than 1 week.
Original: Generated in 0.426 seconds in which 0.418 seconds were spent on a total of 4 queries.
Modified: Generated in 0.435 seconds in which 0.423 seconds were spent on a total of 4 queries.
Search posts newer than 1 month.
Original: Generated in 1.661 seconds in which 1.649 seconds were spent on a total of 4 queries.
Modified: Generated in 0.429 seconds in which 0.421 seconds were spent on a total of 4 queries.
Search posts newer than 1 year.
Original: Generated in 10.039 seconds in which 10.028 seconds were spent on a total of 4 queries.
Modified: Generated in 0.423 seconds in which 0.413 seconds were spent on a total of 4 queries.
Working Under: UBB.Threads 6.4
Mod Status: Beta
Any pre-requisites: mySQL v4.0.1+
Author(s): Ythan
Date: 07/01/04
Credits: N/A
Files Altered: dosearch.php
New Files: None
Database Altered: Yes, indexes created on w3t_Posts
Info/Instructions: Before this search will work, you must create fulltext indexes on your w3t_Posts table. Note that the indexes take up a small amount disk space so don't do this if you are running very low.
Close your boards, backup your w3t_Posts table, then run the following query to add the indexes:
ALTER TABLE w3t_Posts ADD FULLTEXT (B_Subject), ADD FULLTEXT (B_Body), ADD FULLTEXT (B_Subject, B_Body);
Next, edit dosearch.php.
Find:
// ---------------------------------------------------- <br />// A very clumsy hack to specify entire phrase searches <br />. <br />. <br />(select all code between these comments) <br />. <br />. <br />// ---------------------------------------------- <br />// IF we have some results lets make it look nice
Replace that entire block of code with:
// ---------------------------------------------------- <br />// Fulltext search for keywords <br /> <br />if ($Words){ <br /> <br /> if ($where == "sub") { <br /> $query .= "\n AND MATCH (t1.B_Subject) AGAINST ('$Words' IN BOOLEAN MODE)"; <br /> } elseif ($where == "bodysub") { <br /> $query .= "\n AND MATCH (t1.B_Subject,t1.B_Body) AGAINST ('$Words' IN BOOLEAN MODE)"; <br /> } <br /> <br />} <br /> <br /> if ($displaytype == "last") { <br /> $query = $query."\nORDER BY t1.B_Last_Post DESC"; <br /> } <br /> elseif ($displaytype == "main") { <br /> $query = $query."\nAND B_Number = B_Main \nORDER BY B_Posted DESC"; <br /> } <br /> else { <br /> $query = $query."\nORDER BY B_Posted DESC\n"; <br /> } <br /> <br /> $GrabLimit = $Limit +1; <br /> if ($Searchpage == 0) { <br /> $Totalgrab = "LIMIT $GrabLimit"; <br /> } else { <br /> $Startat = $Searchpage * ($Limit - 1) + $Searchpage; <br /> $Totalgrab = "LIMIT $Startat,$GrabLimit"; <br /> } <br /> <br />// Query is dependent upon the limit function <br /> $startpost = 0; <br /> $endpage = 1; <br /> <br /> $query = $query. "\n$Totalgrab"; <br /> <br />// -------------------------------- <br />// Done with query format <br /> <br />// --------------------- <br />// Now execute the query <br /> <br /> $sth = $dbh -> do_query($query); <br /> $rows = $dbh -> total_rows($sth); <br /> <br />// ---------------------------------------------- <br />// IF we have some results lets make it look niceThat's it, enjoy your new super-fast searches! Also, let me say again please post any comments or suggestions.
Hope this helps some people out,
-Y
Disclaimer: Please backup every file that you intend to modify.
If the modification modifies the database, it's a good idea to backup your database before doing so.
Note: If you modify your UBB.Threads code, you may be giving up your right for "official" support from Infopop.If you need official support, you'll need to restore unmodified files.
