Fulltext boolean search v.2.2 for vB
 

Hello all!

Moving here from beta forum
https://vborg.vbsupport.ru/showthread.php?t=62218

This hack makes nearly same for vB3 as
[vB 2.2.x] - Mysql 4 Search hack
https://vborg.vbsupport.ru/showthread.php?t=51716
for vB2 :)

You will need MySQL server v4.0.1 or better (but sometimes it may work on 3.23.xx). After installing you will be able to search with empty native vB index (word and postindex tables) and using modifiers.
Allowed modifiers + are ,-, * and "
All modifiers except * should be used only once for one word (in the beginning and without space).
* it should be used at the end of a word.
For example:
windows unix -> will find messages containing at least one these words.
+windows +unix -> will find messages with both this words.
windows* -> will find "windows", "windowss", "windowssauce" or "windowst".
*indows will NOT find "windows"
"some words" -> will find "some words of wisdom", but will not find "some extra words".
Search phrase length limitations replaced with results number limitation.
Value of old "Search Index Maximum Word Length" used to limit number of posts in the result returned by fulltext search (control panel/Message Searching Options)
Supposed that it must run faster then native vB search :)

History:
v.2.2 [5 Apr 2004]
- search words relevance (when sort by relevance) added at last :)
but little different then native vB (it may not work when searching with * modifiers)
- attempt to fix incompatibility with other hacks
=to upgrade replace code block #5 in search.php with latest one :)

v.2.1 [4 Apr 2004]
- Excluding from search forums with "Index New Posts in Search Engine" option set to "No"

v.2.0 [30 Mar 2004]
-"Similar Threads" now must start working :) (to move from 1.x to 2 just change one more script - functions_search.php)

v.1.9 [29 Mar 2004]
-checking if $query string is not empty before running fill text sql

v.1.8 [20 Mar 2004]
- line numbers and higlight code changed for VB3 Gold
- more tests and error explanations

v.1.7 [9 Mar 2004]
- MySQL error for administrators bug fixed
checking is $not_forumid string exixts before adding it to query

v.1.6 [9 Mar 2004]
- national letters bug fixed
preg_replace("~[^\w\"\-+\* ]~i", "", $query);
was replaced by
preg_replace("~[^\w\xC0-\xFF\"\-+\* ]~i", "", $query);

v.1.5 [8 Mar 2004]
- TABLE_PREFIX bug fixed
- slightly optimised SQL requests

v.1.4 [8 Mar 2004]
- delete_post_index function turned off
- more tests and error explanations

v.1.3 [7 Mar 2004]
- less code because of using native vB $postQueryLogic and $threadQueryLogic conditions
- more tests and error explanations

v.1.2 [7 Mar 2004]
- boolean mode can be turned off in AdminCP ("Allow Search Wild Cards" setting)
- "titles only" search fixed
- limiting number of matches retunned by fulltext search AFTER applying search conditions

v.1.1 [7 Mar 2004]
- HighLight support added

This goes quick

i'll give it a try ;)

Anyone have any idea how efficient this is going to be with a very large board... 3 million posts.

Does it require firstly to index all of the current posts? If so, can anyone estimate how long that is likely to take?

thanks in advance

when you will say
ALTER TABLE post ADD FULLTEXT (title)
it will " index all of the current posts" :)
And not sure about 3 millions, but that request takes about 370 seconds on P4 2400/1G RAM/two SATA 120G Maxtor disks/MySQL 4.0.16 and ~427.000 posts :)

This part of code from block 2 strip national characters in query. E.g., russian characters not passed.

If I comment it - search works.
I'm not guru in RegExp, so can't fix it by myself. Need help.

On all servers that I have ever seen (since php2 :) "\w" match national characters :) But not on in latest PHP versions...
but if you want, you may say
$query = preg_replace("~[^\w\xC0-\xFF\"\-+\* ]~i", "", $query);
:)

Old expression strip russian characters at all.
Second one - left only first character.

true...
Just tested it on 7 hosts and on 3 of them it don't work... I need to update PHP version on my servers more frequently.
And
$query = preg_replace("~[^\w\xC0-\xFF\"\-+\* ]~i", "", $query);
go to v.1.6 :)

Thanks!

anyone have a test site to try with lots of posts?

forum.wbfree.net
~270,000 posts

It looks like you have to register to search on that site? Any other sites? I doubt you want hundreds of us registering just to test out the search ;)

ive got 40000 post not much
forums.webrats.com
i thinking of installing this

heynurse :)
Go to AdminCP and select

Usergroup Manager
[Edit] Unregistered / Not Logged In
check yes for
- Can View Forum
- Can View Others' Threads
- Can Search Forum
after it go to Forum Permissions and select what forums Unregistered users will be able to see, read and search :)

does the above in the instructions mean
either edit the databuild.php or do the editing in the files in [note 3] ?

sorry for my English :)
it means that you may edit functions_databuild.php or comment all calls of that functions in all scripts or don't touch #3 (but then your unused word and postsindex will grow and it will take server processor time:)

thank you
so basicly if i edit databuild my search index will not grow right ?
also what does this querie do
FLUSH TABLE post

as i have no permissions to execute it
but the hack seems to work without it
also does this have an affect on similar threads ?

yes - after editing databuild, index will not grow :)
and FLUSH just clear mysql cache for post table. On some servers
ALTER TABLE post ADD FULLTEXT (title)
runs more then 10 minutes and die with error without FLUSH befor? and finish fine in 3 minutes if FLUSH was executed before. Can't explain why it happen, but it happen sometimes :)

Thansk for this hack John - it's gonna help no end I think :)

Great job!

[high]* Natch installs
[/high]

I also don't have the permissions to run the FLUSH query, but as you say, unless the board is HUGE, and the post table is way way bloated, the FLUSH query is not a *required* query to run for functionality ...

Thanks for the hack :)

I put this hack on my forums which has 1.1 million posts... the search works ok but unfortunately the server load has gone up now.
Current Server Load Averages: 2.75 2.48 2.45 | 161 Users Online (119 members and 42 guests)
It used to be around 1 before this hack was installed. Have you got any ideas on this?

hmm...
Sorry Nitesh, but I had no chance to test it with more then 400.00 posts... And just now have no idea how to fight with it...
do you remember your server load numbers before instslling?

Its aright John :)

It seems my server load is fluctuating now...
Server Load Averages: 1.10 1.40 2.16 | 174 Users Online (135 members and 39 guests).
I?m on a dedicated server so there aren?t any other scripts... I think it maybe just being odd loll... I'll keep an eye on it and keep you informed.

I couldn't get simular threads to work with this either, is that included with this hack?

Anyway thank you for the hard work you've put on this script, saved me about 20 hours of search index rebuilding!

:)
But I still thinking at "simular threads" and have no good (and not heavy for server) solution for it. But feel that it is somewhere near :)

I have almost 2 million posts. Anyone installed this hack on a larger forum? I don't want to have to reindex my search table if this doesn't work. :)

aww...c'mon, be a sport Erwin. Erwin! Erwin!

besides why not to change scripts without touching functions_databuild.php and current words and posts index? :) after it you will be able to return back to standard without reindexing :)

That's true. :) I may try this when I find time.

yes it is buggy :D

I tried on my board, with UTF-8 text, and the highlight is broken (when highlighting utf-8 chars)

:D I may wait for a solution for it, before puting this cool hack in use.

Sorry, but just now I have no idea how to test it with UTF-8 :)
Will think, but can't promice someting...

Hello, i tried this hack but somehow it showed far less results than the normal search (12 <-> 1000).
Currently our forums is on rc2 and I replaced search.php with vb3 gold hacked search - which might be a reason.
Tom

you tried to change number in "Search Index Maximum Word Length" setting in adminCP?
Sorry - asking becase it looks like the most possible reason :)
And if it will continue returning too few results after setting large number there, you may try to drop fulltext index and create it again with repair and optimise requests before and after creation.

I don't understand this ;)
I think the Search Index Maximum Word Length in admincp has nothing to do with mysqls fulltext index (this is limited only by entries in my.cnf regarding the fulltext index) - the maximum word length in vbs acp only limits the entries of the word table...

Recreating the mysqls fulltext index might be an idea - i have to shut down our forums tonight and will try this.
Thank you
Tom

yes :) but see #2 in extra changes part

2. Value of old "Search Index Maximum Word Length" used to limit number of posts in the result returned by fulltext search

I used value of that setting just to free database from searces that may return too much results - sorry :)

JohnWoo, thank you!
LOL, didn't see this ;)

will try it again...

In the process of installing, 1.3 million posts.

alter tables are taking ages ... :(

Will keep you updated.

Still altering... sigh.

Ok, this hack seems to work on our 1.1mio posts board. :up: :up:

I will be able to say something about load after sunday evening, which is one of
our most freuqented days
Thank you for this hack!
Tom

Had to reindex, still indexing, forum's been down most the day ...

This took iirc ~ less than half an hour.. you know that you do not have to reindex using vbulletin? Mysql will create this automatically for you as soon as you enter the sql commands as instructed...
Tom