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!

[gmarik]

7thgenCivic.Com - the answer to the firs two is YES. I'll write you, if I still have some problems, really great instructions ... I'll try (the problem is I have a Russian translation of phpMyAdmin so it's not so easy to undersand what to do.

[ImportPassion]

ahhh...well, you didn't mention that!

[ImportPassion]

So, when is this gonna get out of beta?
Anyone run test w/o the limit options yet?
Anyone have an issues with this at all yet besides those metioned?

D

[Tigga]

Yesterday at 10:44 PM 7thgenCivic.Com said this in Post #93
Anyone run test w/o the limit options yet?

Yep. Seems to work just fine.

[David Bott]

I am sorry to ask, but what is the size of your forum? I ask for removing the limit may not seem like anything to a small site...say less than 1 million post. For adding a limit to any search will greatly help. Thus the question.

[ImportPassion]

here is his forum stats

Members: 3,574 (1 Today)
Threads: 31,967 (8 Today)
Posts: 430,467 (246 Today)

[David Bott]

Thanks...See the issue I raise? Our site...

Threads: 266,104
Posts: 2,322,090
Registered Members: 72,291

We already use a hack does a limit based on the users choice. This hack is great in concept and I am looking to use it. We would just need to modify it to use the user set choice of the limit.

[ImportPassion]

i think the only way you are going to figure it out is if you do it on a dev server. this way you will know. I know it's not the same cause of traffic issues, but it will help.

D

[David Bott]

Actually, the only really way to know is on a live server under full load. People search in different ways not to mention you will end up several, serveral searches running at one time when you have 1400+ on-line.

I am looking to do something like this though, but need to find the time to clsoe the site why the new data elements are being built. That wil ltake some time with 2.3 million posts.

[ImportPassion]

well, there is your answer. I don't think anyone with this hack installed has anywhere near the amount of traffic and members that you do.

[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