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!

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

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