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

[JohnWoo]

sorry, but I can't test it with multibyte language...
But problem may be in too strict input filtering.
Please try to replace
$query = trim(preg_replace("~[\.,:;@=']~", " ", $query));
$query = preg_replace("~[^\w\xC0-\xFF\"\-+\* ]~i", "", trim($query));
with
$query = preg_replace("~['<>]~i", "", trim($query));

but i still don't think that it will help...

[jb605]

No, it didn't work either. It does not work even if I remove that part completely.

I think the issue lies between fulltext itself and my language character. I tried to do an search using

SELECT * FROM post WHERE MATCH(title, pagetext) AGAINST('汉字')

where inside the AGAINST is some chinese character, it returns nothing. Although I know that there are posts with these words. Is fulltext doing a partial match be default?

Thanks for your help.

[motorhaven]

Installed two days ago on a 1.5 million post VB. Works like a charm.

On our database server (dual AMD 2400MP system, 3 gigabytes RAM) we're seeing a load of about .25 during peak now (12.5% load). Used to be about .50. I was more concerned with overall response time, not load, and this has helped with both.

Our front end system (dual AMD 2800MP system, 4 gigabytes RAM) still has a crap load, 1.50 - 2.50 and that's the one regret I have with moving from VB 2.x to VB 3.x. Our front end server used to run about a .75 to 1.5 load.

[Trigunflame]

I have 8+ years in system administration, and could try to help you lower that frontend system load if you wanted, with dual amd 2800, your load should be no where near that high.

[motorhaven]

I've been programming since 1979, sys admin since the early 90s. This is not a case of lack of experience, the system really is loaded down with VBulletin. We're quickly approaching a terabyte of bandwidth per month from the front end server. 10s of millions of page views each month, 1000-1500 users at once in the forums (except this week has been slower due to the Easter/Spring Break holiday).

Vbulletin eats up an enormous number of CPU cycles compared to the prior version. For instance, instead of having the language in the templates, language is now parsed in, adding overhead to every template built.

In the last 24 hours, I've done a few things to reduce load not directly associated with VBulletin. For one, I'm running mod_gzip and it looks like image.php and attachment.php where being compressed (avatar.php was left over from the prior httpd.conf config and is now removed). Not a huge amount of CPU overhead reduced by not compressing these, but every bit helps.

Additionally, at any given time we will have between 20-100 Google, Yahoo and Inktomi spiders crawling the site. That eats up a lot because they fetch more pages per minute than the average real user. I certainly don't want to turn them away, search engine spidering is crucial to success on the web these days....

[kmike]

We are confident that this hack causes some unnecessary mysql tables locking, raising server load to the roof. It was not so with vb2 version of fulltext search hack. Could it be that LEFT JOIN with thread table in the main SELECT causes that locking? VB2 version had a simple

PHP Code:

SELECT postid FROM post WHERE MATCH(pagetext) AGAINST ('+word' IN BOOLEAN MODE)
 ORDER BY dateline DESC 


without any check for forums id's or permissions, and then applied permissions and forums conditionals at the search results display time.

[JohnWoo]

LEFT JOIN don't only add server load (besides it don't add too much - you can try to remove that lLEFT JOIN and look yourself at timings with debug=1)
If you have large forum with many different users and user groups and with many forums invisible for some of that usergroups or users, that LEFT JOIN make fulltext search run faster (and sometimes much faster) because some (or many) posts will be excluded from MATCH => AGAINST (and that part of request takes the most part or server load)
Second reson is limiting mumber or results returned by search. vb2 version of fulltext search first do lookup in ALL posts with limiting number of matches and after it cut (from that already limited number) more posts looking at user permissions. So, I don't think that vb2 fulltext search works as it must work - sorry

[JohnWoo]

and if you start talking about server load, here I have one thing that really takes too much processor time.. My pain in that hack is relevance when seaching with modifiers (IN BOOLEAN MODE).. If you feel that you can live without relevance when seaching with modifiers, you may try to replace string starting from $fulltext_sql with something like it:

$isboolean="'";
	$limit_final = "";
	if ($vboptions['allowwildcards'] ==1 && preg_match("~[\"\-\*+]~i", $query)) {
		$isboolean="' IN BOOLEAN MODE";
	}
	if (strlen($limit_conditions) > 0) {
		$limit_final = "WHERE 1".$limit_conditions;
	}
	$fulltext_sql = "SELECT postid,MATCH (".$what_field.") AGAINST ('".$query.$isboolean.") as relevance FROM ".TABLE_PREFIX."post AS post LEFT JOIN ".TABLE_PREFIX."thread AS thread ON post.threadid=thread.threadid ".$limit_final." HAVING relevance>0 ORDER BY relevance DESC LIMIT 0, ".$vboptions['maxresults'];

And it will save up to 40% server time on some combination of search words

[kmike]

Yes, forum is quite large, about 2,000,000 posts and over 1000 users online in the daytime.

I'll try to dig further, but fact stands as it is: only yesterday on the same hardware with vb2 fulltext search server load was around 2-3 in daytime and less than 1.0 in night time, whereas today it sometimes spikes to 30.0, and I'm seeing hundreds of locked mysql threads besides running search query.

Not acceptable - I had to disable search completely for now (BTW, global "enablesearches" setting in admin CP is currently broken).

[Erwin]

Originally Posted by kmike

Yes, forum is quite large, about 2,000,000 posts and over 1000 users online in the daytime.

I'll try to dig further, but fact stands as it is: only yesterday on the same hardware with vb2 fulltext search server load was around 2-3 in daytime and less than 1.0 in night time, whereas today it sometimes spikes to 30.0, and I'm seeing hundreds of locked mysql threads besides running search query.

Not acceptable - I had to disable search completely for now (BTW, global "enablesearches" setting in admin CP is currently broken).

Mmm... I have exactly the same forums, with 2.2 mil posts and up to 1200 users online at once. I was just about to install this hack, but your tale has given me cause to hesitate. Let me know how it goes.