Mod Name / Version: Search Accelerator v1.6 <br />Version 1.6 <br />-Fixes a bug in UBB 6.5 where maximum search daterange is ignored (thanks chuckee!) <br />Version 1.5 <br />-Adds the ability to sort search results by relevance. <br />-Adds option to search parent posts only. <br />-Fixes bug in UBB 6.5 with searching for asterisks. <br />-Fixes bug in previous version of mod with phrase searches. <br />Version 1.0 <br />-Updated to work with UBB 6.5's search result caching system. <br />Version 0.1 <br />-Initial beta version for UBB 6.4.x. <br /> <br />Description: <br /> <br />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. <br /> <br />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. <br /> <br />Installation Notes: <br /> <br />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. <br /> <br />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. <br /> <br />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. <br /> <br />Benchmarks: <br /> <br />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. <br /> <br />Search term: elephant <br />Settings: Search in subject and body, all forums <br /> <br />Search posts newer than 1 week. <br />Original: Generated in 0.561 seconds in which 0.556 seconds were spent on a total of 8 queries. <br />Modified: Generated in 0.065 seconds in which 0.056 seconds were spent on a total of 8 queries. <br /> <br />Search posts newer than 1 month. <br />Original: Generated in 2.336 seconds in which 2.323 seconds were spent on a total of 8 queries. <br />Modified: Generated in 0.064 seconds in which 0.052 seconds were spent on a total of 8 queries. <br /> <br />Search posts newer than 1 year. <br />Original: Generated in 8.063 seconds in which 8.038 seconds were spent on a total of 8 queries. <br />Modified: Generated in 0.079 seconds in which 0.059 seconds were spent on a total of 8 queries. <br /> <br />Search term: "web browser" <br />Settings: Search in subject and body, all forums <br /> <br />Search posts newer than 1 week. <br />Original: Generated in 0.53 seconds in which 0.526 seconds were spent on a total of 8 queries. <br />Modified: Generated in 0.178 seconds in which 0.172 seconds were spent on a total of 8 queries. <br /> <br />Search posts newer than 1 month. <br />Original: Generated in 2.309 seconds in which 2.303 seconds were spent on a total of 8 queries. <br />Modified: Generated in 0.178 seconds in which 0.17 seconds were spent on a total of 8 queries. <br /> <br />Search posts newer than 1 year. <br />Original: Generated in 12.042 seconds in which 11.988 seconds were spent on a total of 8 queries. <br />Modified: Generated in 0.189 seconds in which 0.176 seconds were spent on a total of 8 queries. <br /> <br />Working Under: UBB.Threads 6.5 <br /> <br />Mod Status: Finished <br /> <br />Any pre-requisites: mySQL v4.0.1+ <br /> <br />Author(s): Ythan <br /> <br />Date: 10/07/04 <br /> <br />Files Altered: <br />/dosearch.php <br />/languages/english/dosearch.php <br />/languages/english/search.php <br />/templates/default/dosearch.tmpl <br />/templates/default/search.tmpl <br /> <br />Database Altered: Yes, indexes created on w3t_Posts.

Pretty impressive stats, thank you <img src="http://www.ubbdev.com/forum/images/graemlins/smile.gif" alt="" />

Josh,<br /><br />Advisable for 6.5 final?<br /><br />Thanks for your input, and thanks Ythan for posting. No offence, just looking for a .threads-expert opinion.<br /><br />Jason <img src="http://www.ubbdev.com/forum/images/graemlins/smile.gif" alt="" />

Nice!<br /><br />Sky - haven't tested yet myself - but I've recently been learning about fulltext searches for MySQL 4 for another project I'm working on. <img src="http://www.ubbdev.com/forum/images/graemlins/smile.gif" alt="" />

I edited my post to reflect a new version of this mod and posted some updated benchmark scores for UBB 6.5. There are a few changes and bugfixes but the most important new feature (in my opinion) is an option to sort search results by relevance. <br /> <br />Sky, I don't think any responsible admin would start messing with their database without looking for some feedback first <img src="http://www.ubbdev.com/forum/images/graemlins/smile.gif" alt="" />. I have the mod installed on my BB if you'd like to see it in action, and fortunately if you want to try it out there isn't a lot of risk involved. The biggest danger I can see would be if your post table is corrupted when you run the alter table query. Even so, I don't think you'd have to worry about data loss, more likely you'd just end up with corrupted indexes. To minimize the risk, I'd run myisamchk -r -n w3t_Posts.MYI first. If you don't have terminal access, then I'd run the query REPAIR TABLE w3t_Posts;. <br /> <br />After installing the mod, if you decide you want to revert to the old search simply restore the unmodified files and run: <br /> <br />ALTER TABLE w3t_Posts DROP INDEX 'B_Subject'; <br />ALTER TABLE w3t_Posts DROP INDEX 'B_Body'; <br />ALTER TABLE w3t_Posts DROP INDEX 'B_Subject_2'; <br /> <br />That should get you back to the original state. <br /> <br />Good luck, if you give it a try I'd be interested to hear how it works for you. <br /> <br />-Y

I did a search for 'yamaha' on your site using your site's search and it returned one result. However, when I used the Google search, it returned 3 pages of results. What's da dilly yo? <img src="http://www.ubbdev.com/forum/images/graemlins/smile.gif" alt="" />

I think you were using the wrong search. We have yet to integrate the forum search with our main site search, so there are 2 separate search pages which is kind of confusing. Incidentally, I haven't even updated the site search to use fulltext indexes yet since the database it's querying is so much smaller. Anyway, you want our forum search, which returns 58 results for 'yamaha'. Google may return even more since we archived a lot of old posts in a separate DB to keep things manageable. Note that you have to be logged in to search, you can use l/p ThreadsDev / ThreadsDev to test. <br /> <br />Anyway I think that explains it, let me know if you have any other questions. <br /> <br />-Y

This has been on my site now for a week and is working great. Hardly any more slow downs. <br />

Hey,<br />can this mod be used with mySQL 3.23.35?<br />I ask this because I saw on this site that fulltext searching is available from 3.23.23 onwards:<br />http://dev.mysql.com/doc/mysql/en/Fulltext_Search.html

Hey -<br /><br />This mod is designed to use boolean fulltext queries so I believe it only works on mySQL 4.0.1+ (see http://dev.mysql.com/doc/mysql/en/Fulltext_Boolean.html). However, if you remove "IN BOOLEAN MODE" from the query in the mod I suspect it might work okay. You just wouldn't get the benefits of boolean modifiers.<br /><br />If you try this out please let me know your results.<br /><br />Peace,<br /><br />-Y

OK, I'm trying to install this modification of yours, and it looks pretty good so far. I just have one question I would like clarified:<br />The instructions mention the following:<br />###<br />### Open /templates/default/dosearch.tmpl<br />###<br /><br />#<br /># Find this:<br />#<br /><br />$currentresults<br /><br /><br />#<br /># Change to this:<br />#<br /><br />$currentresults ($sortinfo).<br /><br /><br />I noticed that the file contains two occurences of "$currentresults". Do both of these need to be changed? I am unsure about this. Also you say "$currentresults ($sortinfo)." - is the period (full stop) at the end supposed to be there or not?<br /><br />Thanks

Hey there -<br /><br />The quick answer to your question is that you probably want to change both instances of $currentresults, and leave the period in place. However in this step you're just editing a template so you don't need to worry about your syntax as much as if you were editing a script.<br /><br />Hope you find this mod beneficial, let me know if you have any other questions!<br /><br />-Y

Hi, thanks for the quick reply. <br />It seems to be working. There is just one issue I found straight away - I'm not sure whether it is a 'bug' with this modification or with Infopop's board, but here it is: <br />If you delete the "1" from the "Newer than" box (i.e. leave it blank) the search is performed on all possible dates, whereas it should in fact be restricted to the maximum date range (in my case it is 6 months) <br />Cheers

Hey chuckee - <br /> <br />Congratulations on uncovering a new UBB bug! This problem should affect all BB's, even those not running my mod. Here's a quick fix: <br /> <br />In dosearch.php find the following line: <br /> <br /> <br /> <br />Under this add: <br /> <br /> <br /> <br />I've updated my modification instructions to reflect this change. Thanks chuckee!

This mods is not work in Cyrillic charset win-1251. I think that problem may be here<br /> <br /><br />Please, help me. How resolved this problem?

I use this and it works great. <br />I do have one question though and I wonder if it's related to this search mod or threads in general. When a user is doing a search, update commands are locked and wait till the user is done searching. Is this normal? Can this be stopped?<br /><br />What happens is when I get several hundred people online updates are bound to happen. At the same time people are doing searches and the locks keep queuing up and before you know it page load times are up to 60-90seconds. Anyway to prevent this?