Mysql 4 Search hack

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!

[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.

[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.