Mysql 4 Search hack

[traekwon]

MySQL 4 Search hack for Vbulletin 2.x.x - version 1.2 beta
Author: PineappleClock (pineappleclock@clockcrew.cc) aim: Janitor61
-------------------------------------

Overview
The MySQL 4 search hack modifies the search functionality in Vbulletin to take advantage of the new SQL functions available in MySQL 4.

Synopsis
This hack allows MySQL to index the posts and threads in your BBS instead of requiring vbulletin to do it.
This hack, when installed, will change the BBS in the following ways:

Vbulletin will not require the 'word' or 'searchindex' tables anymore - saving lots of space - instead it will use the 'post' table as an index.
Vbulletin will not catalog, reference and index every word of every post upon insertion into the database - saving tons of processing.
No need to regenerate the search index ever.
The 'post' table will increase in size by about 35% :(
Searches will be considerably faster, and posting will be somewhat faster.
Search strings will be able to contain phrases, like "lets share a soda", which can include small words that would of been blocked in vbulletin 2.x searches.
Advanced searches, containing included words, optional words, excluded words and exact phrases.


File Listing
install.txt -- the installation howto
simplesearch.txt -- a block of code that replaces a block of code in search.php
searchtemplate.txt -- a vbulletin template used for drawing the search form.

Requirements
MySQL 4.0.1 or newer - required
vbulletin 2.x.x - required (i've only tested it on 2.3.0 though)

How to check your MySQL version:
execute the following query:

SELECT @@global.version



Notes
The installation instructions assumes that you have access to database management software, like phpMyAdmin. If you can't edit your database directly, I can make a php script that will modify your database if there's enough demand.

The Vbulletin team aren't going to put in support for MySQL 4 until version 3.1 - so this hack should be obsolete by then.
If you'd like to see the modified search engine, go here:

http://www.clockcrew.cc/bbs/search.php

I was originally going to distribute the whole modified search.php file, but after reading the rules, im only distributing the modified part. Please let me know if you spot any bugs, etc. I've tested this, but there still are probably bugs i need to work out.

update [Apr 17]: added instructions on uninstalling

good luck!

[Erwin]

Very interesting. What would this do with server loads? Would it increase overall load, or decrease it? I'm impressed.

[Tigga]

Looks nice, but searching for an exact phrase doesn't seem to work on your site. I tried a couple of very common words and each time it said there were no results.

[nuno]

This seems a useful hack, somewhat similar to fastforward's (still unreleased).
Thanks for sharing it. :)

[nuno]

BTW, I'm getting a no permission error at http://www.clockcrew.cc/bbs/search.php. :(
I'll try this tomorrow. I'm off to bed now.
Good night folks. :)

[traekwon]

Today at 05:16 AM Tigga said this in Post #3 (https://vborg.vbsupport.ru/showthread.php?postid=383235#post383235)
Looks nice, but searching for an exact phrase doesn't seem to work on your site. I tried a couple of very common words and each time it said there were no results.

try searching for 'break a leg' (no quotes) and put the option 'display results as posts'


I'm getting a no permission error


Sorry - i had searching off for guests, it's on now.

[traekwon]

Today at 05:15 AM Erwin said this in Post #2 (https://vborg.vbsupport.ru/showthread.php?postid=383234#post383234)
Very interesting. What would this do with server loads? Would it increase overall load, or decrease it? I'm impressed.

From what i've seen, it's decreased my server load. They are using this hack over at forums.somethingawful.com as well - with ~ 1,800 concurrent users, and a lot of people there have mentioned that the site runs faster but YMMV.

I dont really have an accurate benchmark on which to gauge server performance though, but i've noticed my server load averages have been around 0.9 to 2.0 with an avg. of 80 members online - i remember them being a lot higher before, up in the 5s. I'm pretty sure the performance boost is since the server dosent have to do INSERT and SELECTs for each word in every new post thats added.

[Erwin]

What's with the strange music on your site??!! LOL! :)

[Logician]

It looks VERY impressing indeed! As a matter of fact one of the most promising hacks that has been released for sometime. :)

I wish you could make a comparison before and after applying the hack. For instance if you use Teck's microstats hack, it shows searching performance quite good enough. The bottleneck in the default vb searching pushs badly when:
a) you have a big post database.
b) user searches a few common words at the same time. (For instance you have a DVD forum and user searches word "DVD AND MOVIE")
c) User uses wildcard in this search. eg. "dvd* AND movie*"

This search really kills the server in default vb search algorithm even if you are on dedicated. I have experienced searching taking upto 10-15 seconds in such searches in some large boards and when they have a few of such searches, the server gets on to its knees.

It would be terrific to learn the stats for such searches before and after you applied the hack. I really wonder how much it enhances the server load issue.

The 'post' table will increase in size by about 35%
This is nothing to worry about! Tables "search" and "searchindex" has always larger than table "post" so when you get rid of them altogether, you'll still save 65% more space in the server even if you have 35% loss in post table.

Since these 3 tables are the largest tables in your database, %65 more space for them means more than %50 gain in your MYSQL space (thus server space) which is incredible! :)

[Paul]

What would happen in a vb2 to vb3 upgrade once this hack is installed?

[traekwon]

Today at 07:12 PM LoveShack said this in Post #10 (https://vborg.vbsupport.ru/showthread.php?postid=383457#post383457)
What would happen in a vb2 to vb3 upgrade once this hack is installed?

well, the hack would have to be uninstalled. I don't know anything about the vb3 database scheme, since i dont have the sourcecode for it - the only stuff i know about it is what i've read on vbulletin.com, but im positive that they make use of the 'word' and 'searchindex' tables, so if you're planning on upgrading, you would have to uninstall the hack and then rebuild your search index, and then your DB should be reverted back to a pre-hack state.

I erroneously said in the instructions that you could drop the 'word' and 'searchindex' tables, but if you are going to upgrade it's better to just empty the tables, retaining their structure so they can later be rebuilt, or leave them be if you don't care about database disk usage.

I'd have to see vb3 source code before I could verify that this hack would work with it - right now i'd say it's too risky to install on vb3, i'll post an update if i do get to anaylize that source.

Uninstallation would just be the reverse of the instructions, and you would delete search.php and rename search_old.php (your backup) to search.php. You'd then rebuild the search index to poopulate the 'word' and the 'searchindex' tables and revert the searchintro template to its original state. The extra fulltext indexes on 'post' could be removed for some extra diskspace, but they aren't going to hurt anything if you left them, i believe.

I've updated the installation instructions with this information.

[Paul]

If I'd only had the nerve to upgrade mysql. ;) I'll consider adding this hack. It sounds like it might also lead to an answer on the best way to implement guest username searching.

[nuno]

It seems that both 'word' and 'searchindex' tables keep on going, they keep increasing, what's going on here? :confused:

[mute]

edit: we've got it running over here and it rocks! :)

[nuno]

Are both tables empty?

[mute]

Today at 10:19 PM nuno said this in Post #15 (https://vborg.vbsupport.ru/showthread.php?postid=383640#post383640)
Are both tables empty?

Yup, I emptied both tables (searchindex & word) and they stayed empty. My buddy had hacked the search.php improperly, and that was the problem we ran into.

[nuno]

Okay, it's all fixed now. :)

[alexi]

I just installed this and it seems to be working very well!
I don't have phpmyadmin on my database server because I don't even have apache running on it. I was able to make the database changes through Mysql Control Center. It works pretty well.

[AlexanderT]

04-17-03 at 05:32 AM traekwon said this in Post #1 (https://vborg.vbsupport.ru/showthread.php?postid=383228#post383228)http://www.clockcrew.cc/bbs/search.php


Bandwidth Limit Exceeded
The server is temporarily unable to service your request due to the site owner reaching his/her bandwidth limit. Please try again later.

--- Looks like a lot of people who like your script visited your site ...

[alexi]

If anyone wants to see it in action and traekwon's site is busy feel free to check it out at www.disboards.com.
It is working wonderfully and I am already getting ton's of positive feedback from my users.

[AlexanderT]

Seems to work great! (currently running it on a testforum)

One small side note: In the install.txt instruction, you mention once OPEN FILE: /functions.php and once OPEN FILE: /admin/functions.php. Well, there is no /functions.php and I think the instruction is duplicated there (so skip the /functions.php part).

[AlexanderT]

Another Q:

Should I still keep Minimum Word Length (default 4)?

I noticed that when using the new search hack any word <minimum word length would result in a "Sorry - no matches. Please try some different terms." but not in the "The search term you specified (fin) is under the minimum word length...." text.

What about the other Search options in admincp? Do they still have any functionality? Namely:

Floodcheck - Minimum time between searches
Minimum Word Length
Maximum Word Length
Allow Wild Cards?


Greets
Alex

[rake]

Excellent hack! Reading through the fulltext searching page in the mysql manual, i stumbled accross Patrick O'Lone's reply:

It should be noted in the documentation that IN
BOOLEAN MODE will almost always return a
relevance of 1.0. In order to get a relevance that is
meaningful, you'll need to:
<BR/><BR/>
SELECT MATCH('Content') AGAINST ('keyword1
keyword2') as Relevance FROM table WHERE MATCH
('Content') AGAINST('+keyword1 +keyword2' IN
BOOLEAN MODE) HAVING Relevance > 0.2 ORDER
BY Relevance DESC
<BR/><BR/>
Notice that you are doing a regular relevance query
to obtain relevance factors combined with a WHERE
clause that uses BOOLEAN MODE. The BOOLEAN
MODE gives you the subset that fulfills the
requirements of the BOOLEAN search, the relevance
query fulfills the relevance factor, and the HAVING
clause (in this case) ensures that the document is
relevant to the search (i.e. documents that score
less than 0.2 are considered irrelevant). This also
allows you to order by relevance.
<BR/><BR/>
This may or may not be a bug in the way that IN
BOOLEAN MODE operates, although the comments
I've read on the mailing list suggest that IN
BOOLEAN MODE's relevance ranking is not very
complicated, thus lending itself poorly for actually
providing relevant documents. BTW - I didn't notice
a performance loss for doing this, since it appears
MySQL only performs the FULLTEXT search once,
even though the two MATCH clauses are different.
Use EXPLAIN to prove this.

So i decided to modify this hack to get the most relevant results on top, but i always get an error when running the query. I've attached my simplesearch. Is there anything wrong with it? :D

[AlexanderT]

I am curious to know if anyone has already run a little benchmark test on this one.

Unfortunately, my forum is too small for the hack to make any noticeable difference.

[alexi]

I don't have any hard numbers to prove it but going by feel I think it has made a really big difference. I have recieved numerous user comments about how much faster search seems.
My load averages are way down but I am not sure how much of that is Mysql 4 and how much is the search.

[jbourke]

Well, this was a happy coincidence.

I had just done some maintenance and needed to rebuild my search index. I have about 100,000 threads in my forum so it was taking forever.

About halfway through I decided to look for a better way to go about it, hoping there was a script or something. Rebuilding the search index is a real pain for a large vb forum.

I came across this hack and it encouraged me to upgrade to mysql 4 and give it a shot. The upgrade to v4 was a cinch, and the hack installed in a straightforward way. It took about an hour and a half to run the SQL commands.

This is the most involved hack I've installed. I run several of my own private hacks but tend to avoid other people's hacks. This one is a winner.

I have between 500 and 700 people online during the day. My servers are seeing decreased load. Unfortunately I didn't capture any statistics beforehand, but from memory I believe my database load is about 2/3rds of what it was. This is significant in my situation as I'm always running out of hardware and vb doesn't scale well beyond 2 servers.

My forums are at: http://www.rcgroups.com

(the busy forums are in the "electric" section)

Jim

[mute]

Well, I'm loving this hack too.

My load is considerably lower on the webserver thats not running my DB, and on the DB server its more or less the same despite my queries per second being about 30% higher (due to increased site traffic). Seems as though people are surfing more, maybe because the search doesn't suck anymore? :)

I was posting over at vb.com in the forums trying to help convince them that they need to support a fulltext search option.

Obviously it would require another search.php, because they are still supporting mysql 3.x, but they are all "the performance increase and db size decrease are negligible"..

I'm not saying that the fulltext search is THAT fantastic, but I can safely say that I don't think I'll be upgrading to vb3 when it comes out until someone has hacked together a fulltext search like this.

One thing I have noticed though is that some people are complaining that some searches are returning no results. When I asked them what they were searching for, they replied that they were searching for phrases like "dye" and "ir3". Now these are pretty commonly searched for terms in the paintball world, and it would be cool if this would work, despite them being short and sort of common words..

Is this new search engine still sensitive to short search phrases? Am I making any sense? :)

[rake]

Today at 06:14 PM mute said this in Post #27 (https://vborg.vbsupport.ru/showthread.php?postid=385209#post385209)
Well, I'm loving this hack too.

My load is considerably lower on the webserver thats not running my DB, and on the DB server its more or less the same despite my queries per second being about 30% higher (due to increased site traffic). Seems as though people are surfing more, maybe because the search doesn't suck anymore? :)

I was posting over at vb.com in the forums trying to help convince them that they need to support a fulltext search option.

Obviously it would require another search.php, because they are still supporting mysql 3.x, but they are all "the performance increase and db size decrease are negligible"..

I'm not saying that the fulltext search is THAT fantastic, but I can safely say that I don't think I'll be upgrading to vb3 when it comes out until someone has hacked together a fulltext search like this.

One thing I have noticed though is that some people are complaining that some searches are returning no results. When I asked them what they were searching for, they replied that they were searching for phrases like "dye" and "ir3". Now these are pretty commonly searched for terms in the paintball world, and it would be cool if this would work, despite them being short and sort of common words..

Is this new search engine still sensitive to short search phrases? Am I making any sense? :)

mySQL 4, by default, doesn't return results which appea in more than half of the rows, if i'm not mistaking. But this shouldn't apply to boolean searches.

[mute]

Yeah, my board has ~1.5 million posts, its unlikely that those phrases are occuring in > 1/2 the posts..

[AlexanderT]

Today at 08:49 PM rake said this in Post #28 (https://vborg.vbsupport.ru/showthread.php?postid=385259#post385259)
mySQL 4, by default, doesn't return results which appea in more than half of the rows, if i'm not mistaking. But this shouldn't apply to boolean searches.
What exactly do you mean by "half of the rows"?

[jbourke]

The mysql docs state that anything 3 or less characters is not indexed.

Jim

[mute]

Well there you have it, thanks Jim!

/me rtfms

[jbourke]

Hmmm...this hack only returns the first few pages of results.

I'm going to have to revert to the old search engine if there isn't a way to fix this.

Jim

[jbourke]

These two lines:

$posts=$DB_site->query("SELECT postid FROM post WHERE MATCH(title) AGAINST ('$querywc' IN BOOLEAN MODE) ORDER BY dateline DESC LIMIT 25");

and

$posts=$DB_site->query("SELECT postid FROM post WHERE MATCH(pagetext) AGAINST ('$querywc' IN BOOLEAN MODE) ORDER BY dateline DESC LIMIT 100");

both have limit statements.

Is there a problem with taking these out?

I don't mind experimenting but I'm a bit nervous because I have a pretty large forum.

Jim

[Brad]

Jim, increse them as you see fit, if you think you've gone to high revert them.

[Da`Nacho]

A few problems I am noticing, please someone verify with their setup:

When viewing a thread contained in search results the search terms are only hilighted on the first page of the thread.

When searching for a PHRASE and viewing a thread contained in those results, the search terms are not hilighted at all in the results and when you click to visit page 2 in the thread it reloads the first page and the &hilight part of the URL is empty.

I don't recall these things happening with the original search schema. :confused:

[Da`Nacho]

Gah it's driving me crazy... somehow spaces or something are being inserted in the query string, thats whats causing the screwups but I don't understand where it's coming from.

IE a normal one word search will end up having a + behind it... hard to explain.

If I remove the hack it's totally fine...

I got so frustrated that I totally reinstalled a clean copy of vBulletin 2.3.0 and reperformed the hack - same results. :(

[Tigga]

Just installed the hack and it seems to be working pretty good. Searches are much better, and posting does seem a little faster. :)

I can confirm Da`Nacho's problems though. It doesn't seem to highlight the words, and when you search for an exact phrase, the page numbers no longer work. It seems the link to page 2 (or any other) on showthread.php is somehow missing the "pagenumber=X" part of the url. After you search and go to the thread though, if you take out the "&highlight=xxxx" in the url and reload the page, it seems to work fine.
Other than that though it seems to work great. :)

[Da`Nacho]

Today at 05:31 PM Tigga said this in Post #38 (https://vborg.vbsupport.ru/showthread.php?postid=386146#post386146)
Just installed the hack and it seems to be working pretty good. Searches are much better, and posting does seem a little faster. :)

I can confirm Da`Nacho's problems though. It doesn't seem to highlight the words, and when you search for an exact phrase, the page numbers no longer work. It seems the link to page 2 (or any other) on showthread.php is somehow missing the "pagenumber=X" part of the url. After you search and go to the thread though, if you take out the "&highlight=xxxx" and reload the page, it seems to work fine.
Other than that though it seems to work great. :)


Yeah, I spent 5 hours last night redoing and redoing this hack and I just dont' know what the problem is. Anyone who wants to see what we mean can visit www.candidforums.com and use the search (it's enabled for guests).

We are running MySQL 4.0.12-0 and php 4.3.1 on Apache 1.3.27.

Anyone have any clues?

[Tigga]

Well I can tell you where the problem is coming from... After you perform a search, if you hold your mouse over the thread, you'll see it's linking to something like:
http://yoursite.com/forum/showthread.php?s=&threadid=x&highlight="what+you+searched+for"
The " marks in the url seem to be what's causing the problem. When you click on the thread it replaces the " marks with %2B in your browsers url. If you remove the %2B from the url then it seems to work as it should. Not sure how to fix it yet, but hopefully that gives traekwon a little more insight as to what the problem is. ;)

I also just noticed another strange problem that seems to be coming from the %2B being in the url... After I performed the search and went to one of the threads, a smilie image there had border="0" alt=""> after the image. When I viewed the source there it has this for the html code...


<td bgcolor="#160056" colspan="2" align="left" valign="top"><p><font class="nf">he saw 'the ring'<br /><font color="red"></font>
<font color="red"></font><br /><font color="red"></font>
<font color="red"></font><img src="/forum/images/smilies/frown.gif"<font color="red"></font> border="0"<font color="red"></font> alt="<font color="red"></font>"><font color="red"></font></font></p></td>

Obviously it's trying to highlight quite a few things there that it shouldn't be. ;)

[Erwin]

Installed, and it works great.

This is a revolutionary hack for vB. I am quite sure future versions will incorporate this with it. :) Search is faster, posting is slightly faster (since we don't have to add to the searchindex and word tables)... 250 members online at once with no problems so far (I shall wait for my peak times) ...

As for the page and highlight errors - they don't apply to me since I don't use highlights as all my search result pages are parsed into a non-dynamic format like this:

http://www.yourdomain.com/search/137498-2.html

And it works great with this hack. :)

Thanks for this one.

[Erwin]

I've managed to make my Search Thread hack work with this, which is great.

Very simple.

1) Install my search hack here first before installing this hack:

https://vborg.vbsupport.ru/showthread.php?s=&threadid=45142

If you've already installed my hack, that's fine. Go to next step.

2) Then, install this hack (after installing my hack first).

3) Then, find this block of code (which is from this hack) in search.php:


// check for existing query:
if ($getsearch=$DB_site->query_first("SELECT searchid FROM search WHERE showposts='".intval($showposts)."' AND query='".addslashes($wheresql)."' AND postids='".addslashes($goodpostlist)."' AND querystring='".addslashes($masterquery)."'")) {
$DB_site->query("UPDATE search SET dateline=".time().", userid=$bbuserinfo[userid], ipaddress='".addslashes($ipaddress)."' WHERE searchid=$getsearch[searchid]");
$searchid=$getsearch[searchid];
} else {
// insert query into db
$DB_site->query("INSERT INTO search (searchid,query,postids,dateline,querystring,showp osts,userid,ipaddress) VALUES (NULL,'".addslashes($wheresql)."','".addslashes($goodpostlist)."',".time().",'".addslashes($masterquery)."','".intval($showposts)."',$bbuserinfo[userid],'".addslashes($ipaddress)."')");
$searchid=$DB_site->insert_id();
}


Replace with this:


// search thread - Erwin

if (!$threadid) {
$threadid="0";
}
// check for existing query:
if ($getsearch=$DB_site->query_first("SELECT searchid FROM search WHERE showposts='".intval($showposts)."' AND query='".addslashes($wheresql)."' AND postids='".addslashes($goodpostlist)."' AND querystring='".addslashes($masterquery)."'")) {
$DB_site->query("UPDATE search SET dateline=".time().", userid=$bbuserinfo[userid], ipaddress='".addslashes($ipaddress)."', threadid=$threadid WHERE searchid=$getsearch[searchid]");
$searchid=$getsearch[searchid];
} else {
// insert query into db
$DB_site->query("INSERT INTO search (searchid,query,postids,dateline,querystring,showp osts,userid,ipaddress,threadid) VALUES (NULL,'".addslashes($wheresql)."','".addslashes($goodpostlist)."',".time().",'".addslashes($masterquery)."','".intval($showposts)."',$bbuserinfo[userid],'".addslashes($ipaddress)."',$threadid)");
$searchid=$DB_site->insert_id();
}

// search thread - Erwin


Alternative, if you've already installed this hack first, you can install my hack after, and skip PHP steps (1) and (2) in my instructions, and instead use Step (2) above, then follow the instructions (3)-(6) in my instructions. :)

Thanks, my Search Thread is much faster. I notice this new search is smarter and ignores common words too. :)

[Brad]

Erwin, I was just thinking about that, thanks for the fix! :)

[Da`Nacho]

Today at 04:25 AM Erwin said this in Post #42 (https://vborg.vbsupport.ru/showthread.php?postid=387299#post387299)
Alternative, if you've already installed this hack first, you can install my hack after, and skip PHP steps (1) and (2) in my instructions, and instead use Step (2) above, then follow the instructions (3)-(6) in my instructions. :)

Thanks, my Search Thread is much faster. I notice this new search is smarter and ignores common words too. :)


Erin, I get a parse error on line 402 when I try doing this hack...

line 402 just happens to be the following query:

$DB_site->query("UPDATE search SET dateline=".time().", userid=$bbuserinfo[userid], ipaddress='".$ipaddress)."', threadid=,$threadid WHERE searchid=$getsearch[searchid]");


Any ideas?

[Erwin]

Oops! I made a mistake -

Change:


threadid=,$threadid


To:


threadid=$threadid


:) I left a comma there for some reason.

[Erwin]

I've fixed my post as well.

[Da`Nacho]

Darn... I still get a parse error on the same line...

/me continues pulling hair out

[Erwin]

That's odd. Are you sure you removed the extra comma? Redo the instructions again from the beginning. Also, let's not hijack this support thread - post more questions in the Search Thread hack support thread.

[Da`Nacho]

Erwin helped me get it working, thanks!

Now if we could all figure out how this phantom string is showing up for some of us in the MySQL 4 hack it would be peaches! :)

[papoo]

04-24-03 at 01:06 AM Tigga said this in Post #40 (https://vborg.vbsupport.ru/showthread.php?postid=386158#post386158)
Well I can tell you where the problem is coming from... After you perform a search, if you hold your mouse over the thread, you'll see it's linking to something like:
http://yoursite.com/forum/showthread.php?s=&threadid=x&highlight="what+you+searched+for"
The " marks in the url seem to be what's causing the problem. When you click on the thread it replaces the " marks with %2B in your browsers url. If you remove the %2B from the url then it seems to work as it should. Not sure how to fix it yet, but hopefully that gives traekwon a little more insight as to what the problem is. ;)

I also just noticed another strange problem that seems to be coming from the %2B being in the url... After I performed the search and went to one of the threads, a smilie image there had border="0" alt=""> after the image. When I viewed the source there it has this for the html code...



to solve this problem i have made this modification

after this line in the search.php

$search[querystring]=urlencode($search[querystring]);
i add this line
$search[querystring]=ereg_replace('[%2B]','',$search[querystring]);

[Tigga]

Good idea papoo. I was trying to figure out a way to make sure the " marks didn't appear in the url, but your method seems to work just fine. :)

[Yapluka]

Papoo is the king of ereg_replace :laugh:
Very nice hack ! Thanks so much... :banana:

[Da`Nacho]

The ereg_replace method above doesn't work for me, and if it did I'd be kind of leary of it since it's basically taking a step backward and really not solving the problem. (no offense to Papoo)

I've been comparing the original vBulletin search.php and the changes Traekwon made, seeing if I can figure out what the problem is for some of us. From what I can see it involves problems with quotes (") and extra plus signs (+) being inserted in queries, even single word queries... it's like the search thinks there is more than one word no matter what.

Of particular interest is the urldecode() lines that Traekwon seems to have removed... if I add it back for the masterquery (ie $masterquery = urldecode($masterquery); I can sort of get it working better, but not perfect. :(

Where did Traekwon drop off the face of the planet to, anyway. :p :)

[pstdgt]

this worked for me:


$search[querystring]=urlencode($search[querystring]);

should be replaced with:

$search[querystring]=ereg_replace('[%2B|+]','',$search[querystring]);

[SemperFidelis]

This seriously sounds like a great hack.
:)

But Im unable to test at your site (http://www.clockcrew.cc/bbs/search.php) as I keep getting this :

There seems to have been a slight problem with the clockcrew bbs database.
Please try again by pressing the refresh button in your browser.

An E-Mail has been dispatched to our Technical Staff, who you can also contact if the problem persists.

We apologise for any inconvenience.

[Da`Nacho]

Welp, I'm afraid I must uninstall this hack.

The lack of search terms being parsed right in the results (for highlighting) is just too annoying, and the only fixes I and others like me have been able to come up with are ereg_replace workarounds. It's like taking 2 steps forward and one step back...

What a shame... :(

[Erwin]

I've stopped using the highlight term feature for over 1 year now since I have static URLs. :) So it's not a big loss.

[David Bott]

Hello...

I have an interest in using this hack for AVS Forum and TiVo Community Forum for the sites are large and past the point that the normal can be run without hurting the system.

In the site that the author lists as a test to see it in action...I find that "search for exact phrase:" does not seem to work at all and only returns that nothing is found. Not even if you try one one.

So..anyone know anything about this or has anyone seen the author of the hack posting anything, or updates, about this hack?

Thanks

[Erwin]

That feature works for me - I am able to find exact phrases - note that this hack automatically stops you from finding words that are too common automatically.

[David Bott]

Pondering...so if you search for a phrase that has a word that is too common in it, then it will return nothing.

Also...

I think people are finding this search faster because of the limit statements in the search statements. Has anyone tried removing them all together? My guess is it would slow down again.

I have had to add limit statements to all of my searches and give the user an option to search with the number of returns, with the default being the lowest number of returns. The issue though when they look for more than one word. It will find the first set (say 500) and then look for the second word in only that 500 found. Gets worse with more that 2 words as you may guess.

In any case, we have found it is not the search itself, but the large number of returns that can sometime happen and TMP files need to be made for this. During that time, tables are locked and this the major slowdown on busy sites.

We just went MySQL ver 4 on new server and are currently going to be testing. I only hope MySQL 4 is much better in this reguard.

[Erwin]

This hack bypasses vB and uses MySQL 4's own search abilities. It does seem to have sped up my site.

[David Bott]

I am sorry, but do you still have the limit statements in the code? If so, this is one the reason for your speed incrase. For you no longer search the entire database, but only up until X returns are found, then it stops. If you have them still in, remove them and see. MySQL has always been able to search, they have just improved it in ver 4 with the caching for the most part.

Not sure, but your database may not be large enough to see if their is a difference in speed. Not sure though. But just adding limit statments to our code, user selectable, made our site 100% faster all around due to the tables not locking as often on large returns.

Still looking for some real data and the author to return to support the hack. Even on his own site the phrase search does not seem to work.

Thanks

[Tigga]

The phrase search works just fine for me. And I definitely notice a faster search time as well. :)

[David Bott]

Question again...do you have the LIMIT statements still in the code? If I had to guess...I would say you do. For after teasting about 10 words on your site...all returned under 100 results. Thus I am guessing this is all why you see the search speed increase. Thanks

[Boofo]

This is what I am getting when I do a search for the word "code":

codebackground}; color:#EEEEFF; border:1px solid #273549" border="0" cellspacing="0" cellpadding="5"> ? Originally posted by Gandalf

testing <font color="red">html</font> for Gandalf
?


Does anyone know how to keep it from searching the PHP, Code and quotes boxes code?

[Boofo]

Can someone please tell me how to "drop the two fulltext indexes" in the post table?

Edit: I am having major problems after un-installing this hack. I followed the directions exactly, but now when I search for words like color, background, code or font it highlights the words in my PHP, code and quote boxes code. It throws off the boxes and everything? Can someone please tell me what is happening and maybe how to fix it?

Edit again: NM, I figured it out.

[Tigga]

You're right David. I removed the LIMIT statements from 2 of the queries and it seems to work much better now. :D

[Boofo]

Which 2 queries? And I figured out a way to get rid of the plus sign before the word searched if anyone is interested.

[Tigga]

If you search for LIMIT 25 and LIMIT 100 those should be the correct 2.

[Boofo]

Ok, I already took those 2 out. You said from 2 of the queries. I wanted to make sure I had the right 2. Thanks! ;)

I finally got it working great! The highlight doesn't mess with the other stuff now. I had to redo the highlight statement, but that fixed it. Even on a small site, there is a major difference in the searching speed now.

[Erwin]

Good work making the highlight work. :)

[Boofo]

Thank you, sir. :) It was a blast figuring it out. ;)

[gmarik]

It is the best hack ever.
I was looking for it for half a year.
I even started a thread here about this on vb.com

[Da`Nacho]

06-04-03 at 06:30 PM Boofo said this in Post #72 (https://vborg.vbsupport.ru/showthread.php?postid=404520#post404520)
Thank you, sir. :) It was a blast figuring it out. ;)


Care to provide instructions in this thread?

[traekwon]

Sorry i haven't been around lately, been trying to learn the whole .NET thing and clockcrew.cc is sort of a ghost town now :\

I believe that taking out the LIMITs on the search query would slow it down, but i haven't done any benchmarking.

here's what I did to fix the highlighting problem:

on line 626 (approx) of search.php you will find:


if ($search[querystring]!="") {
$search[querystring]=urlencode($search[querystring]);
$highlightwords="&highlight=$search[querystring]";
} else {
$highlightwords="";
}


replace it with:


if ($search[querystring]!="") {
$search[querystring]=preg_replace("/ \-(.*) /"," ",$search[querystring]);
$search[querystring]=preg_replace("/ \-(.*)$/","",$search[querystring]);
$search[querystring]=str_replace("+","",$search[querystring]);
$search[querystring]=str_replace('"',"",$search[querystring]);
$search[querystring]=urlencode($search[querystring]);
$highlightwords="&highlight=$search[querystring]";
} else {
$highlightwords="";
}


i believe that would work, but the highlighting engine can't highlight phrases - I will get to work on fixing that, thats part of the underlying system not the search.php AFAIK


thanks for the kind words guys :) oh and gmarik, wheres the thread you started on vbulletin.com?

[Boofo]

That seems to work good except for any words that have a period after it (i.e. charm.)

[gmarik]

A good idea would be optimizing "session" and "adminlog", maybe auto prune after some time, lets say month?


Can anbybody tell me what to do in these lines? A full jibberish ...

6. click on 'fulltext' in the 'pagetext' row to create a fulltext index of 'pagetext' - this might take a very long while.
7. click on 'fulltext' in the 'title' row to create a fulltext index of 'title'
8. once the operation has completed, click on the 'operations' tab and optimize the table. - this might take a while.

[gmarik]

search.php?s=&action=showresults&searchid=714&sortby=lastpost&sortorder=descending

It shows this and a blank screen when I make a search. Windows (http://www.mysql.com/downloads/download.php?file=Downloads%2FMySQL-4.1%2Fmysql-4.1.0-alpha.zip&pick=mirror)

[ImportPassion]

what is the status on this? Eeveryone got it working ok?
I have a huge boad, almost at 1 million posts and would love to be able to do this hack, but kind of leary with the few problems ppl are having.

D

[mute]

working great here, we're almost to the 2 million post mark..

[Dontom]

For those who wish to change their preset minimum word-length:
(Standard is 4 characters)
you have to set a var in your my.cnf:
set-variable = ft_min_word_len=3


More info: http://www.mysql.com/doc/en/Fulltext_Fine-tuning.html

Thanks for this great hack! Search is faster than before, so ist posting , too.
The most notably difference: Editing has a huge difference now - this caused always peaks and took very long - now its instant
(My Forums: >600.000 Posts > 12.000 Users)
Database size went from 2 GB to 1,6 GB

Tom

[Erwin]

Working well with my private forums with over 1 million posts. Working so well, that this is one reason why I may not upgrade to vB3. :)

[David Bott]

Hi All...

Again I feel I must ask. Have your removed the limit statements from the code? It will be faster then the standard code just with the limit statements in it. You can just ad the same statements to the current code and you will have the same affect.

Thus I still looking to see if it is faster without them. At this point the only thing I see is the removel of the larger indexes.

Don't get me wrong, getting rid of the LARGE indexes is a good thing....I am all for it. But I still am not sure about the benefit at this point. Not to mention.,..if you go to ver 3, then the hack may not work and their may or may not be an up date to it. Then you need to rebuild the indexes again and that is something that takes out site over a day and a half to do with 2 million+ posts.

I am sorry, but I am just looking for some good remark on it. I really want to try this hack, but I have yet to see anything that jumps out and says this is a btter way other then the dumping of the large indexes.

Thank you kindly.

http://www.avsforum.com For thouse who do not know me.

Threads: 261,927 Posts: 2,287,189 Registered Members: 71,568

David

[ImportPassion]

Ok, I don't think this works

I went to
http://www.clockcrew.cc/bbs/showthread.php?s=&threadid=15485

I took the phrase
"evil clocks in my movie" from that thread

I went to search for exact phrase and nothing comes up. I even tried just "evil clocks" and nothing came up.

What gives?

D

[gmarik]

Today at 07:13 AM Erwin said this in Post #82 (https://vborg.vbsupport.ru/showthread.php?postid=410894#post410894)
Working well with my private forums with over 1 million posts. Working so well, that this is one reason why I may not upgrade to vB3. :)


You just followed the instructions? Nothing worked for me. Can you translate the readme file into English (from gibberish)? So people could use this great hack. And why do you think you could not integrate it in vb3?

[Dontom]

Instructions seemed very clear to me - where Do you have problems?
Tom

[sajjid]

can someoen please verify theses instructions for or is that a mistake here what is says

**Search for the string "DELETE FROM searchindex" - around line 827
**and change this line:

$DB_site->query("DELETE FROM searchindex WHERE postid=$getfirstpost[postid]");

**to:

//$DB_site->query("DELETE FROM searchindex WHERE postid=$getfirstpost[postid]");

the last line which starts with // is that correct ? or should it start with $

[Boofo]

Today at 09:51 PM sajjid said this in Post #87 (https://vborg.vbsupport.ru/showthread.php?postid=411243#post411243)
can someoen please verify theses instructions for or is that a mistake here what is says

**Search for the string "DELETE FROM searchindex" - around line 827
**and change this line:

$DB_site->query("DELETE FROM searchindex WHERE postid=$getfirstpost[postid]");

**to:

//$DB_site->query("DELETE FROM searchindex WHERE postid=$getfirstpost[postid]");

the last line which starts with // is that correct ? or should it start with $

It means to comment that line out with the //

[gmarik]

06-20-03 at 08:15 PM Dontom said this in Post #86 (https://vborg.vbsupport.ru/showthread.php?postid=411083#post411083)
Instructions seemed very clear to me - where Do you have problems?
Tom

Like to transform the DB tables?
Really, I sat down and looked at it several ways, but did not find a way to make all the indexes and all the things. Can anybody, I beg you, write the steps, concerning the Db changes in clear, step by step English? :)

[ImportPassion]

ok, well first question.
Do you know what phpmyadmin is and do you have it?

If yes to both, then the instructions are pretty damn clear

2. in phpMyAdmin, select your vbulletin database, and locate the 'post' table.
the drop down on the left contains ur databases, select the one that contains the vb tables. then scroll down the right side to u see the 'Post' table and select 'Properties'.

3. skipped

4. click on the 'change' link in the 'pagetext' row to edit the datatype.
in the 'Field' column, find 'pagetext'. Under the 'Action' column for this field, click 'Change'

5. change the type of the 'pagetext' column from 'mediumtext' to 'text', and click 'save' - be patient this might take a while.
on this page under the 'Type' column it will say 'mediumtext' change that dropdown to 'text' then click 'save'. it will take time depending on how many posts you have

6. click on 'fulltext' in the 'pagetext' row to create a fulltext index of 'pagetext' - this might take a very long while.
i think that will bring you back to the page from step 2, if not, follow step 2 again. in the 'Field' column, find 'pagetext'. Under the 'Action' column for this field, click 'Fulltext'

7. click on 'fulltext' in the 'title' row to create a fulltext index of 'title'
i think that will bring you back to the page from step 2, if not, follow step 2 again. in the 'Field' column, find 'title'. Under the 'Action' column for this field, click 'Fulltext'

8. once the operation has completed, click on the 'operations' tab and optimize the table. - this might take a while.
at the top you will see a blue tab called 'operations', click it and then click 'Optimize table' at the bottom under 'Table maintenance'

that's it.
if you don't understand this, then you shouldn't be doing this hack. my 2cents.

D

[gmarik]

7thgenCivic.Com - the answer to the firs two is YES. I'll write you, if I still have some problems, really great instructions ... I'll try (the problem is I have a Russian translation of phpMyAdmin so it's not so easy to undersand what to do.

[ImportPassion]

ahhh...well, you didn't mention that!

[ImportPassion]

So, when is this gonna get out of beta?
Anyone run test w/o the limit options yet?
Anyone have an issues with this at all yet besides those metioned?

D

[Tigga]

Yesterday at 10:44 PM 7thgenCivic.Com said this in Post #93 (https://vborg.vbsupport.ru/showthread.php?postid=415410#post415410)
Anyone run test w/o the limit options yet?

Yep. Seems to work just fine. :)

[David Bott]

I am sorry to ask, but what is the size of your forum? I ask for removing the limit may not seem like anything to a small site...say less than 1 million post. For adding a limit to any search will greatly help. Thus the question.

[ImportPassion]

here is his forum stats

Members: 3,574 (1 Today)
Threads: 31,967 (8 Today)
Posts: 430,467 (246 Today)

[David Bott]

Thanks...See the issue I raise? Our site...

Threads: 266,104
Posts: 2,322,090
Registered Members: 72,291

We already use a hack does a limit based on the users choice. This hack is great in concept and I am looking to use it. We would just need to modify it to use the user set choice of the limit.

[ImportPassion]

i think the only way you are going to figure it out is if you do it on a dev server. this way you will know. I know it's not the same cause of traffic issues, but it will help.

D

[David Bott]

Actually, the only really way to know is on a live server under full load. People search in different ways not to mention you will end up several, serveral searches running at one time when you have 1400+ on-line. :)

I am looking to do something like this though, but need to find the time to clsoe the site why the new data elements are being built. That wil ltake some time with 2.3 million posts.

[ImportPassion]

well, there is your answer. I don't think anyone with this hack installed has anywhere near the amount of traffic and members that you do.

[David Bott]

Thus the concern on why people kept saying is was so much faster and me mentioning the limit statment in the code.

[Erwin]

I can't wait for this to be standard in vB3.

[ImportPassion]

If you want to add words to the stoplist (this is a defined list of words that it will NOT index) read this:

The stopword list can be loaded from file specified by ft_stopword_file variable. See section 4.5.7.4 SHOW VARIABLES. Rebuild your FULLTEXT indexes after modifying the stopword list. (This variable is only available from MySQL version 4.0.10 and onwards)

here is a list of stopwords that are default.

http://www.databasejournal.com/features/mysql/article.php/1578331

I don't agree with some, like "outside" and all the written numbers.

D

[M.C.]

nice idea! but, will that hack keep all functions like search in forum category, search by username and etc.?

[msimplay]

i get this error check thread please

https://vborg.vbsupport.ru/showthread.php?s=&postid=425838

[ImportPassion]

I think you don't have the hack installed right cause I can't search for anything on your site.

[msimplay]

oops sorry i just realised guest searchin was turned off i'll turn it back on :P

problem fixed i searched for the answer within the thread sorry for wasting time :o

[WCW Fan]

So is this Hack safe to install, it sounds like and awesome hack I just want to be on the safe side though

[dwh]

I tried searching on the first few sites that have this installed. Search results were really slow. Do the people who have installed this still feel that good about it? Especially for large forums?

[gmarik]

I tried searching on the first few sites that have this installed. Search results were really slow. Do the people who have installed this still feel that good about it? Especially for large forums?
Another good idea would be to make it work for vB3, it's comming closer and closer, year 2004 is the one to rule it.

[forumdude]

Another good idea would be to make it work for vB3, it's comming closer and closer, year 2004 is the one to rule it.
Just wondering if anyone knows of work being done on this hack for vb3 compatibility. I'm not sure that I can even upgrade to vb3 until this hack is available for vb3.

[MilleniumClock]

Just wondering if anyone knows of work being done on this hack for vb3 compatibility. I'm not sure that I can even upgrade to vb3 until this hack is available for vb3.

lol, i doubt it, this original hack was made for clockcrew.cc, a site which i was part of, hence my name MilleniumClock, Traekwon was the owner and the guy who posted this for piney, and pineappleclock did most, then the site went down, Traekwon dissapeared and hasn't been seen since, and pineappleclock hasn't been around for a while. SOOO.... i seriously doubt it if traekwon and pineapple re-release this for vb3

but things can happen, so you can always keep your fingers crossed :-p

[KevinM]

I've got this hack installed and working fine, however i do have one problem.

I use a gateway script (Gilbys newsgroup hack) to import newsgroup posts, and since installing this hack these new posts are not showing up in the search results.

There is only one line in the newsgroup script which refers to searching of posts, which is:

indexpost($postid,1);

Does anyone know what I need to change this too?

[forumdude]

lol, i doubt it, this original hack was made for clockcrew.cc, a site which i was part of, hence my name MilleniumClock, Traekwon was the owner and part coder, and pineappleclock did most, then the site went down, Traekwon dissapeared and hasn't been seen since, and pineappleclock pretty much left the clockcrew. SOOO.... i seriously doubt it if traekwon and pineapple re-release this for vb3

but things can happen, so you can always keep your fingers crossed :-p



Nice, the worst case scenario played out.


I suppose on a positive note its good that vbulletin plans to add this feature into the stock software in 3.1(i think?). The downside is that those of us who require such a feature are forced to be patient and wait for it (been waiting for vb3 for what seems like a lifetime anyways right?), meh.

[msimplay]

i wish this hack for vb3 would come out

[ImportPassion]

vb 3.1 will have it built in from what i hear

[msimplay]

well to be honest i dont care what vb 3.1 has coz judging on the length of time for vb3 to come out 3.1 is more then a year away

[ImportPassion]

not really the point, iwas just stating that cause I doubt someone will do it if it is planned for 3.1

[msimplay]

yeh i know what u mean but still vb 3.1 is a long way off

[Brad]

I plan on making this for my forum but it is on a long to do list so whenever I get around to it ill do it.

[msimplay]

Pleasee pleaaaaaase release it :D

[forumdude]

Pleasee pleaaaaaase release it :D


:rolleyes:

[forumdude]

here ya go guys: https://vborg.vbsupport.ru/showthread.php?p=471468#post471468


let yourself be heard!

[Swamper]

WOW!!!

Had I known this hack existed I would have installed it a LOOOOOOOOOONG time ago...The performance increase is insane on my server that has 500-700+ users on it 24/7... Searches were always a bad bottleneck (even on a 10k rpm scsi hd) - but this thing is gonna buy me some time before I go to a 15k scsi (or raid setup)

...sticking with vb 2.3.x for now, I don't like 3 at all and the hacks I made would take a while to put into 3.x

[sabret00the]

it would be great is jelsoft released this an offical hack i.e. they will move in this direction but not just yet.

[Swamper]

I made small changes to the code (removing the LIMIT on the search query - it wasn't finding all the results otherwise) - and adding an if statement to handle 'search within this thread' hack.

Also, I set the minimum word length for fulltext search to '3' ... but searching for 3 letter words is sloooowww.. not recommended, but otherwise, my forum is flying now!

[edin]

if ($searchuser!="" and strlen($searchuser)<=3 and $exactname!="yes") {
eval("standarderror(\"".gettemplate("error_searchnametooshort")."\");");
}


This part isn't working for me. We have limited our search index to 4 words, however I can still search for less words ending up with the "No results" message (the settings in the vB control panel has no effect). Please help me getting the "Minimum word length" function to work.

[Parker Clack]

edin:

The example that you have from above is the limit on the search user. To set the limit on size of the words for the rest of the search you will need to set the var in my.cnf to

set-variable=ft_min_word_len=3

More info is at : http://www.mysql.com/doc/en/Fulltext_Fine-tuning.html

Parker

[edin]

edin:

The example that you have from above is the limit on the search user. To set the limit on size of the words for the rest of the search you will need to set the var in my.cnf to

set-variable=ft_min_word_len=3

More info is at : http://www.mysql.com/doc/en/Fulltext_Fine-tuning.html

Parker

Hi Parker,

Thanks for your reply. We have already set the limit to four words in my.cnf, however if a user search for a string with three words he gets the error message "Sorry - no matches". Instead I would like to have the message "Sorry - search for at least four words"... for example.

The limit on the search user or the "error_searchnametooshort" does not work for us.

You can try it out here: http://forum.sweclockers.com/search.php. It's in Swedish, but the layout is the same.