Register Members List Search Today's Posts Mark Forums Read

Reply
 
Mod Options
Mysql 4 Search hack Details »
Mysql 4 Search hack
Mod Version: 1.00, by traekwon (Member) traekwon is offline
Developer Last Online: Apr 2006 I like it Show Printable Version Email this Page

This modification is in the archives.
vB Version: 2.2.x Rating: (1 vote - 5.00 average) Installs: 18
Released: 17 Apr 2003 Last Update: Never Downloads: 11
Not Supported Is in Beta Stage  

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:

Block Disabled:      (Update License Status)  
Suspended or Unlicensed Members Cannot View Code.


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!

Download Now

Only licensed members can download files, Click Here for more information.

Show Your Support

  • To receive notifications regarding updates -> Click to Mark as Installed.
  • This modification may not be copied, reproduced or published elsewhere without author's permission.
Comments
  #2  
Old 17 Apr 2003, 06:15
Erwin's Avatar
Erwin Erwin is offline
 
Join Date: Jan 2002
Very interesting. What would this do with server loads? Would it increase overall load, or decrease it? I'm impressed.
Reply With Quote
  #3  
Old 17 Apr 2003, 06:16
Tigga's Avatar
Tigga Tigga is offline
 
Join Date: Dec 2001
Real name: Brian
Looks nice, but searching for an exact phrase doesn't seem to work on your site. I tried a couple of very common words and each time it said there were no results.
__________________
Brian Gunter
vBadvanced
Reply With Quote
  #4  
Old 17 Apr 2003, 06:16
nuno's Avatar
nuno nuno is offline
 
Join Date: Oct 2001
This seems a useful hack, somewhat similar to fastforward's (still unreleased).
Thanks for sharing it.
__________________
Powered by: vBulletin
Reply With Quote
  #5  
Old 17 Apr 2003, 06:29
nuno's Avatar
nuno nuno is offline
 
Join Date: Oct 2001
BTW, I'm getting a no permission error at http://www.clockcrew.cc/bbs/search.php.
I'll try this tomorrow. I'm off to bed now.
Good night folks.
__________________
Powered by: vBulletin
Reply With Quote
  #6  
Old 17 Apr 2003, 07:20
traekwon traekwon is offline
 
Join Date: Mar 2002
Today at 05:16 AM Tigga said this in Post #3
Looks nice, but searching for an exact phrase doesn't seem to work on your site. I tried a couple of very common words and each time it said there were no results.
try searching for 'break a leg' (no quotes) and put the option 'display results as posts'

I'm getting a no permission error
Sorry - i had searching off for guests, it's on now.
Reply With Quote
  #7  
Old 17 Apr 2003, 07:33
traekwon traekwon is offline
 
Join Date: Mar 2002
Today at 05:15 AM Erwin said this in Post #2
Very interesting. What would this do with server loads? Would it increase overall load, or decrease it? I'm impressed.
From what i've seen, it's decreased my server load. They are using this hack over at forums.somethingawful.com as well - with ~ 1,800 concurrent users, and a lot of people there have mentioned that the site runs faster but YMMV.

I dont really have an accurate benchmark on which to gauge server performance though, but i've noticed my server load averages have been around 0.9 to 2.0 with an avg. of 80 members online - i remember them being a lot higher before, up in the 5s. I'm pretty sure the performance boost is since the server dosent have to do INSERT and SELECTs for each word in every new post thats added.
Reply With Quote
  #8  
Old 17 Apr 2003, 07:57
Erwin's Avatar
Erwin Erwin is offline
 
Join Date: Jan 2002
What's with the strange music on your site??!! LOL!
Reply With Quote
  #9  
Old 17 Apr 2003, 11:36
Logician's Avatar
Logician Logician is offline
 
Join Date: Nov 2001
It looks VERY impressing indeed! As a matter of fact one of the most promising hacks that has been released for sometime.

I wish you could make a comparison before and after applying the hack. For instance if you use Teck's microstats hack, it shows searching performance quite good enough. The bottleneck in the default vb searching pushs badly when:
a) you have a big post database.
b) user searches a few common words at the same time. (For instance you have a DVD forum and user searches word "DVD AND MOVIE")
c) User uses wildcard in this search. eg. "dvd* AND movie*"

This search really kills the server in default vb search algorithm even if you are on dedicated. I have experienced searching taking upto 10-15 seconds in such searches in some large boards and when they have a few of such searches, the server gets on to its knees.

It would be terrific to learn the stats for such searches before and after you applied the hack. I really wonder how much it enhances the server load issue.

The 'post' table will increase in size by about 35%
This is nothing to worry about! Tables "search" and "searchindex" has always larger than table "post" so when you get rid of them altogether, you'll still save 65% more space in the server even if you have 35% loss in post table.

Since these 3 tables are the largest tables in your database, %65 more space for them means more than %50 gain in your MYSQL space (thus server space) which is incredible!
__________________
Logician's Blog | WebTemplates for vb 4.x : VB Integrated Content Management System
Reply With Quote
  #10  
Old 17 Apr 2003, 20:12
Paul Paul is offline
 
Join Date: Jan 2002
What would happen in a vb2 to vb3 upgrade once this hack is installed?
Reply With Quote
  #11  
Old 17 Apr 2003, 20:50
traekwon traekwon is offline
 
Join Date: Mar 2002
Today at 07:12 PM LoveShack said this in Post #10
What would happen in a vb2 to vb3 upgrade once this hack is installed?
well, the hack would have to be uninstalled. I don't know anything about the vb3 database scheme, since i dont have the sourcecode for it - the only stuff i know about it is what i've read on vbulletin.com, but im positive that they make use of the 'word' and 'searchindex' tables, so if you're planning on upgrading, you would have to uninstall the hack and then rebuild your search index, and then your DB should be reverted back to a pre-hack state.

I erroneously said in the instructions that you could drop the 'word' and 'searchindex' tables, but if you are going to upgrade it's better to just empty the tables, retaining their structure so they can later be rebuilt, or leave them be if you don't care about database disk usage.

I'd have to see vb3 source code before I could verify that this hack would work with it - right now i'd say it's too risky to install on vb3, i'll post an update if i do get to anaylize that source.

Uninstallation would just be the reverse of the instructions, and you would delete search.php and rename search_old.php (your backup) to search.php. You'd then rebuild the search index to poopulate the 'word' and the 'searchindex' tables and revert the searchintro template to its original state. The extra fulltext indexes on 'post' could be removed for some extra diskspace, but they aren't going to hurt anything if you left them, i believe.

I've updated the installation instructions with this information.
Reply With Quote
  #12  
Old 17 Apr 2003, 21:51
Paul Paul is offline
 
Join Date: Jan 2002
If I'd only had the nerve to upgrade mysql. I'll consider adding this hack. It sounds like it might also lead to an answer on the best way to implement guest username searching.
Reply With Quote
  #13  
Old 18 Apr 2003, 00:29
nuno's Avatar
nuno nuno is offline
 
Join Date: Oct 2001
It seems that both 'word' and 'searchindex' tables keep on going, they keep increasing, what's going on here?
__________________
Powered by: vBulletin
Reply With Quote
  #14  
Old 18 Apr 2003, 01:35
mute mute is offline
 
Join Date: Dec 2002
edit: we've got it running over here and it rocks!

Last edited by mute; 18 Apr 2003 at 03:17.
Reply With Quote
  #15  
Old 18 Apr 2003, 03:19
nuno's Avatar
nuno nuno is offline
 
Join Date: Oct 2001
Are both tables empty?
__________________
Powered by: vBulletin
Reply With Quote
Reply


Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)
 
Mod Options

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off


New To Site? Need Help?

All times are GMT. The time now is 00:23.

Layout Options | Width: Wide Color: