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!

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

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