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]

Installed, and it works great.

This is a revolutionary hack for vB. I am quite sure future versions will incorporate this with it. Search is faster, posting is slightly faster (since we don't have to add to the searchindex and word tables)... 250 members online at once with no problems so far (I shall wait for my peak times) ...

As for the page and highlight errors - they don't apply to me since I don't use highlights as all my search result pages are parsed into a non-dynamic format like this:

http://www.yourdomain.com/search/137498-2.html

And it works great with this hack.

Thanks for this one.

[Erwin]

I've managed to make my Search Thread hack work with this, which is great.

Very simple.

1) Install my search hack here first before installing this hack:

https://vborg.vbsupport.ru/showthrea...threadid=45142

If you've already installed my hack, that's fine. Go to next step.

2) Then, install this hack (after installing my hack first).

3) Then, find this block of code (which is from this hack) in search.php:

PHP Code:

  // check for existing query:
  if ($getsearch=$DB_site->query_first("SELECT searchid FROM search WHERE showposts='".intval($showposts)."' AND query='".addslashes($wheresql)."' AND postids='".addslashes($goodpostlist)."' AND querystring='".addslashes($masterquery)."'")) {
    $DB_site->query("UPDATE search SET dateline=".time().", userid=$bbuserinfo[userid], ipaddress='".addslashes($ipaddress)."' WHERE searchid=$getsearch[searchid]");
    $searchid=$getsearch[searchid];
  } else {
                // insert query into db
                $DB_site->query("INSERT INTO search (searchid,query,postids,dateline,querystring,showposts,userid,ipaddress) VALUES (NULL,'".addslashes($wheresql)."','".addslashes($goodpostlist)."',".time().",'".addslashes($masterquery)."','".intval($showposts)."',$bbuserinfo[userid],'".addslashes($ipaddress)."')");
                $searchid=$DB_site->insert_id();
  } 


Replace with this:

PHP Code:

// search thread - Erwin

  if (!$threadid) {
  $threadid="0";
  }
    // check for existing query:
  if ($getsearch=$DB_site->query_first("SELECT searchid FROM search WHERE showposts='".intval($showposts)."' AND query='".addslashes($wheresql)."' AND postids='".addslashes($goodpostlist)."' AND querystring='".addslashes($masterquery)."'")) {
    $DB_site->query("UPDATE search SET dateline=".time().", userid=$bbuserinfo[userid], ipaddress='".addslashes($ipaddress)."', threadid=$threadid WHERE searchid=$getsearch[searchid]");
    $searchid=$getsearch[searchid];
  } else {
                // insert query into db
                $DB_site->query("INSERT INTO search (searchid,query,postids,dateline,querystring,showposts,userid,ipaddress,threadid) VALUES (NULL,'".addslashes($wheresql)."','".addslashes($goodpostlist)."',".time().",'".addslashes($masterquery)."','".intval($showposts)."',$bbuserinfo[userid],'".addslashes($ipaddress)."',$threadid)");
                $searchid=$DB_site->insert_id();
  }

// search thread - Erwin 


Alternative, if you've already installed this hack first, you can install my hack after, and skip PHP steps (1) and (2) in my instructions, and instead use Step (2) above, then follow the instructions (3)-(6) in my instructions.

Thanks, my Search Thread is much faster. I notice this new search is smarter and ignores common words too.

[Brad]

Erwin, I was just thinking about that, thanks for the fix!

[Da`Nacho]

Today at 04:25 AM Erwin said this in Post #42
Alternative, if you've already installed this hack first, you can install my hack after, and skip PHP steps (1) and (2) in my instructions, and instead use Step (2) above, then follow the instructions (3)-(6) in my instructions.

Thanks, my Search Thread is much faster. I notice this new search is smarter and ignores common words too.

Erin, I get a parse error on line 402 when I try doing this hack...

line 402 just happens to be the following query:

PHP Code:

    $DB_site->query("UPDATE search SET dateline=".time().", userid=$bbuserinfo[userid], ipaddress='".$ipaddress)."', threadid=,$threadid WHERE searchid=$getsearch[searchid]"); 


Any ideas?

[Erwin]

Oops! I made a mistake -

Change:

PHP Code:

threadid=,$threadid 


To:

PHP Code:

threadid=$threadid 


I left a comma there for some reason.

[Erwin]

I've fixed my post as well.

[Da`Nacho]

Darn... I still get a parse error on the same line...

/me continues pulling hair out

[Erwin]

That's odd. Are you sure you removed the extra comma? Redo the instructions again from the beginning. Also, let's not hijack this support thread - post more questions in the Search Thread hack support thread.

[Da`Nacho]

Erwin helped me get it working, thanks!

Now if we could all figure out how this phantom string is showing up for some of us in the MySQL 4 hack it would be peaches!

[papoo]

04-24-03 at 01:06 AM Tigga said this in Post #40
Well I can tell you where the problem is coming from... After you perform a search, if you hold your mouse over the thread, you'll see it's linking to something like:
http://yoursite.com/forum/showthread...d=x&highlight="what+you+searched+for"
The " marks in the url seem to be what's causing the problem. When you click on the thread it replaces the " marks with %2B in your browsers url. If you remove the %2B from the url then it seems to work as it should. Not sure how to fix it yet, but hopefully that gives traekwon a little more insight as to what the problem is.

I also just noticed another strange problem that seems to be coming from the %2B being in the url... After I performed the search and went to one of the threads, a smilie image there had border="0" alt=""> after the image. When I viewed the source there it has this for the html code...

to solve this problem i have made this modification

after this line in the search.php

$search[querystring]=urlencode($search[querystring]);

i add this line

$search[querystring]=ereg_replace('[%2B]','',$search[querystring]);