Previous Thread
Next Thread
Print Thread
Rate Thread
Joined: Oct 2002
Posts: 24
Newbie
Newbie
Offline
Joined: Oct 2002
Posts: 24
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.
Attachments
120834-srchaccl.txt (0 Bytes, 153 downloads)

Sponsored Links
Joined: Mar 2000
Posts: 21,079
Likes: 3
I type Like navaho
I type Like navaho
Joined: Mar 2000
Posts: 21,079
Likes: 3
Pretty impressive stats, thank you


- Allen wavey
- What Drives You?
Joined: Dec 2003
Posts: 123
Junior Member
Junior Member
Offline
Joined: Dec 2003
Posts: 123
Josh,

Advisable for 6.5 final?

Thanks for your input, and thanks Ythan for posting. No offence, just looking for a .threads-expert opinion.

Jason

Joined: Nov 2001
Posts: 10,369
I type Like navaho
I type Like navaho
Joined: Nov 2001
Posts: 10,369
Nice!

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.

Joined: Oct 2002
Posts: 24
Newbie
Newbie
Offline
Joined: Oct 2002
Posts: 24
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.

Sky, I don't think any responsible admin would start messing with their database without looking for some feedback first . 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;.

After installing the mod, if you decide you want to revert to the old search simply restore the unmodified files and run:

ALTER TABLE w3t_Posts DROP INDEX 'B_Subject';
ALTER TABLE w3t_Posts DROP INDEX 'B_Body';
ALTER TABLE w3t_Posts DROP INDEX 'B_Subject_2';

That should get you back to the original state.

Good luck, if you give it a try I'd be interested to hear how it works for you.

-Y

Sponsored Links
Joined: Aug 2000
Posts: 1,609
Addict
Addict
Offline
Joined: Aug 2000
Posts: 1,609
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?

Joined: Oct 2002
Posts: 24
Newbie
Newbie
Offline
Joined: Oct 2002
Posts: 24
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.

Anyway I think that explains it, let me know if you have any other questions.

-Y

Joined: Oct 2002
Posts: 71
Power User
Power User
Offline
Joined: Oct 2002
Posts: 71
This has been on my site now for a week and is working great. Hardly any more slow downs.

Last edited by VWDerf; 11/23/2004 12:45 AM.
Joined: Mar 2004
Posts: 26
Junior Member
Junior Member
Offline
Joined: Mar 2004
Posts: 26
Hey,
can this mod be used with mySQL 3.23.35?
I ask this because I saw on this site that fulltext searching is available from 3.23.23 onwards:
http://dev.mysql.com/doc/mysql/en/Fulltext_Search.html

Joined: Oct 2002
Posts: 24
Newbie
Newbie
Offline
Joined: Oct 2002
Posts: 24
Hey -

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.

If you try this out please let me know your results.

Peace,

-Y

Sponsored Links
Joined: Mar 2004
Posts: 26
Junior Member
Junior Member
Offline
Joined: Mar 2004
Posts: 26
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:
The instructions mention the following:
###
### Open /templates/default/dosearch.tmpl
###

#
# Find this:
#

$currentresults


#
# Change to this:
#

$currentresults ($sortinfo).


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?

Thanks

Joined: Oct 2002
Posts: 24
Newbie
Newbie
Offline
Joined: Oct 2002
Posts: 24
Hey there -

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.

Hope you find this mod beneficial, let me know if you have any other questions!

-Y

Joined: Mar 2004
Posts: 26
Junior Member
Junior Member
Offline
Joined: Mar 2004
Posts: 26
Hi, thanks for the quick reply.
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:
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)
Cheers

Joined: Oct 2002
Posts: 24
Newbie
Newbie
Offline
Joined: Oct 2002
Posts: 24
Hey chuckee -

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:

In dosearch.php find the following line:

Code
 <br />	$rangeseconds = 0; <br />


Under this add:

Code
 <br />	if (!$newerval && !$olderval){ <br />		$newerval = $config['rangevalue']; <br />		if ($config['rangetype'] == "days") { <br />			$newertype = "d"; <br />		} else if ($config['rangetype'] == "weeks") { <br />			$newertype = "w"; <br />		} else if ($config['rangetype'] == "months") { <br />			$newertype = "m"; <br />		} else if ($config['rangetype'] == "years") { <br />			$newertype = "y"; <br />		} <br />	} <br />


I've updated my modification instructions to reflect this change. Thanks chuckee!

Joined: Jun 2001
Posts: 43
User
User
Offline
Joined: Jun 2001
Posts: 43
This mods is not work in Cyrillic charset win-1251. I think that problem may be here
Code
 preg_match_all("/(\+*|\-*)(['A-Za-z0-9]{1,}|\"{1}[ 'A-Za-z0-9]{1,}\"{1})/", $Words, $SplitWords); 


Please, help me. How resolved this problem?

Joined: Oct 2002
Posts: 71
Power User
Power User
Offline
Joined: Oct 2002
Posts: 71
I use this and it works great.
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?

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?


Link Copied to Clipboard
Donate Today!
Donate via PayPal

Donate to UBBDev today to help aid in Operational, Server and Script Maintenance, and Development costs.

Please also see our parent organization VNC Web Services if you're in the need of a new UBB.threads Install or Upgrade, Site/Server Migrations, or Security and Coding Services.
Recommended Hosts
We have personally worked with and recommend the following Web Hosts:
Stable Host
bluehost
InterServer
Visit us on Facebook
Member Spotlight
isaac
isaac
California
Posts: 1,157
Joined: July 2001
Forum Statistics
Forums63
Topics37,573
Posts293,925
Members13,849
Most Online5,166
Sep 15th, 2019
Today's Statistics
Currently Online
Topics Created
Posts Made
Users Online
Birthdays
Top Posters
AllenAyres 21,079
JoshPet 10,369
LK 7,394
Lord Dexter 6,708
Gizmo 5,833
Greg Hard 4,625
Top Posters(30 Days)
Top Likes Received
isaac 82
Gizmo 20
Brett 7
WebGuy 2
Top Likes Received (30 Days)
None yet
The UBB.Developers Network (UBB.Dev/Threads.Dev) is ©2000-2024 VNC Web Services

 
Powered by UBB.threads™ PHP Forum Software 8.0.0
(Preview build 20221218)