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!

[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/featu...le.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/showthrea...&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

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

Originally Posted by 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?

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.