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!

[Dontom]

For those who wish to change their preset minimum word-length:
(Standard is 4 characters)
you have to set a var in your my.cnf:
set-variable = ft_min_word_len=3


More info: http://www.mysql.com/doc/en/Fulltext_Fine-tuning.html

Thanks for this great hack! Search is faster than before, so ist posting , too.
The most notably difference: Editing has a huge difference now - this caused always peaks and took very long - now its instant
(My Forums: >600.000 Posts > 12.000 Users)
Database size went from 2 GB to 1,6 GB

Tom

[Erwin]

Working well with my private forums with over 1 million posts. Working so well, that this is one reason why I may not upgrade to vB3.

[David Bott]

Hi All...

Again I feel I must ask. Have your removed the limit statements from the code? It will be faster then the standard code just with the limit statements in it. You can just ad the same statements to the current code and you will have the same affect.

Thus I still looking to see if it is faster without them. At this point the only thing I see is the removel of the larger indexes.

Don't get me wrong, getting rid of the LARGE indexes is a good thing....I am all for it. But I still am not sure about the benefit at this point. Not to mention.,..if you go to ver 3, then the hack may not work and their may or may not be an up date to it. Then you need to rebuild the indexes again and that is something that takes out site over a day and a half to do with 2 million+ posts.

I am sorry, but I am just looking for some good remark on it. I really want to try this hack, but I have yet to see anything that jumps out and says this is a btter way other then the dumping of the large indexes.

Thank you kindly.

http://www.avsforum.com For thouse who do not know me.

Threads: 261,927 Posts: 2,287,189 Registered Members: 71,568

David

[ImportPassion]

Ok, I don't think this works

I went to
http://www.clockcrew.cc/bbs/showthre...threadid=15485

I took the phrase
"evil clocks in my movie" from that thread

I went to search for exact phrase and nothing comes up. I even tried just "evil clocks" and nothing came up.

What gives?

D

[gmarik]

Today at 07:13 AM Erwin said this in Post #82
Working well with my private forums with over 1 million posts. Working so well, that this is one reason why I may not upgrade to vB3.

You just followed the instructions? Nothing worked for me. Can you translate the readme file into English (from gibberish)? So people could use this great hack. And why do you think you could not integrate it in vb3?

[Dontom]

Instructions seemed very clear to me - where Do you have problems?
Tom

[sajjid]

can someoen please verify theses instructions for or is that a mistake here what is says

**Search for the string "DELETE FROM searchindex" - around line 827
**and change this line:

$DB_site->query("DELETE FROM searchindex WHERE postid=$getfirstpost[postid]");

**to:

//$DB_site->query("DELETE FROM searchindex WHERE postid=$getfirstpost[postid]");

the last line which starts with // is that correct ? or should it start with $

[Boofo]

Today at 09:51 PM sajjid said this in Post #87
can someoen please verify theses instructions for or is that a mistake here what is says

**Search for the string "DELETE FROM searchindex" - around line 827
**and change this line:

$DB_site->query("DELETE FROM searchindex WHERE postid=$getfirstpost[postid]");

**to:

//$DB_site->query("DELETE FROM searchindex WHERE postid=$getfirstpost[postid]");

the last line which starts with // is that correct ? or should it start with $

It means to comment that line out with the //

[gmarik]

06-20-03 at 08:15 PM Dontom said this in Post #86
Instructions seemed very clear to me - where Do you have problems?
Tom

Like to transform the DB tables?
Really, I sat down and looked at it several ways, but did not find a way to make all the indexes and all the things. Can anybody, I beg you, write the steps, concerning the Db changes in clear, step by step English?

[ImportPassion]

ok, well first question.
Do you know what phpmyadmin is and do you have it?

If yes to both, then the instructions are pretty damn clear

2. in phpMyAdmin, select your vbulletin database, and locate the 'post' table.
the drop down on the left contains ur databases, select the one that contains the vb tables. then scroll down the right side to u see the 'Post' table and select 'Properties'.

3. skipped

4. click on the 'change' link in the 'pagetext' row to edit the datatype.
in the 'Field' column, find 'pagetext'. Under the 'Action' column for this field, click 'Change'

5. change the type of the 'pagetext' column from 'mediumtext' to 'text', and click 'save' - be patient this might take a while.
on this page under the 'Type' column it will say 'mediumtext' change that dropdown to 'text' then click 'save'. it will take time depending on how many posts you have

6. click on 'fulltext' in the 'pagetext' row to create a fulltext index of 'pagetext' - this might take a very long while.
i think that will bring you back to the page from step 2, if not, follow step 2 again. in the 'Field' column, find 'pagetext'. Under the 'Action' column for this field, click 'Fulltext'

7. click on 'fulltext' in the 'title' row to create a fulltext index of 'title'
i think that will bring you back to the page from step 2, if not, follow step 2 again. in the 'Field' column, find 'title'. Under the 'Action' column for this field, click 'Fulltext'

8. once the operation has completed, click on the 'operations' tab and optimize the table. - this might take a while.
at the top you will see a blue tab called 'operations', click it and then click 'Optimize table' at the bottom under 'Table maintenance'

that's it.
if you don't understand this, then you shouldn't be doing this hack. my 2cents.

D