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!

[msimplay]

Pleasee pleaaaaaase release it

[forumdude]

Originally Posted by msimplay

Pleasee pleaaaaaase release it

[forumdude]

here ya go guys: https://vborg.vbsupport.ru/showthrea...468#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]

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

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.