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

[Erwin]

I'm waiting for kmike's version - why reinvent the wheel?

kmike, did you just use the vB2 code? Coz if you did, I can try to modify it to fit too.

[ImportPassion]

Here is some excellent info that everyone should look at.

http://dev.mysql.com/doc/mysql/en/Fu...ne-tuning.html

[Erwin]

My database is fine-tuned already. No matter how much you fine-tune, if your database is big, a LEFT JOIN is a bad query to have.

[ImportPassion]

wasn't meant to be about that. About changing the minimum and maximum word length and other stuff like the stop word list.

[Erwin]

Ahhh... thanks. I see now. Interesting... do you have any particular settings that work for you?

kmike, do post your code once you have them ready.

[JohnWoo]

If you want to remove left joins together with revelance why not to say just

$isboolean="'";
if ($vboptions['allowwildcards'] ==1 && preg_match("~[\"\-\*+]~", $query)) {
	$isboolean="' IN BOOLEAN MODE";
}
$fulltext_sql = "SELECT postid FROM ".TABLE_PREFIX."post WHERE MATCH (".$what_field.") AGAINST ('$query".$isboolean.") LIMIT 0, ".$vboptions['maxresults'];

?
or in one string

$fulltext_sql = "SELECT postid FROM 
	".TABLE_PREFIX."post WHERE MATCH 
	(".$what_field.") AGAINST 
	('$query'". iif($vboptions['allowwildcards'] ==1 && preg_match("~[\"\-\*+]~", $query), ' IN BOOLEAN MODE', '').") 
	LIMIT 0, ".$vboptions['maxresults'];

[ScottW23]

Just wanted to say that we installed this hack on our 950,000 post VB3 board with good results so far. Searches were slow with relevance enabled so I disabled it and searches seem snappy.

[ScottW23]

If you want to remove left joins together with revelance why not to say just

Yes why not?! <Slaps JohnWoo> Works great, even faster now.

[JohnWoo]

Still want to say few words for left joins
Lets say that you have large board with many forums, usergroups and complex access level. After it lets suppose that somebody with common access level (just registered) will search for some common phrase (for example "+javascript +flash").
On one board, where i tested it, such search with no left joins return nothing because fulltext query find first 200 matches in forums invisible to beginner members and stop on it. Showresult page (looking on current member access level) hide all that 200 matches from search result and with great enthusiasm say that nothing found On same forum search with left joins return complete set of 200 matches (from other forums visible to beginner members) for same search phrase and may return more if "maxresult" will be set to larger number.
But if your forum access level structure is not too complex, you may remove left join part without visible consequence

[Erwin]

Awesome, John Woo. Your new query has fixed my slow_query/ mysql locking/ apache crashing problem (as far as I can tell with testing).

I'm using the first code example, just because the original code is like that. Thanks!