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

[kmike]

Thinking about two post tables idea, it could go really well along with mysql replication.
Idea: set up second mysql server (even on the same physical machine), set it up to replicate post table from main forums db. All updates to that second mysql server will be handled by only one slave thread, and it could be locked freely without any effect on first mysql server perfomance.
Since JohnWoo's hack uses two tables in the main query, it appears that thread table need to be replicated along with post table.
Changes to current hack code would be really small: initialize another db connection and fire query to a second db instead of main one.

I don't plan to implement this any time soon, though accidentally we have workng replication of our forum db to another server. I'm polishing a vb2-like hack of this (JohnWoo's) hack . It eliminates LEFT JOIN and always searches IN BOOLEAN MODE. Had to drop search by relevance though, I highly doubt someone used it even once though.

[cerebro]

i will try..and see what happen. Allway can come back .

[cerebro]

i dont have to wait to much...

and work Fine whit 3.0.1

mysql> ALTER TABLE post ADD FULLTEXT (title);
Query OK, 1762278 rows affected (4 min 2.67 sec)
Records: 1762278 Duplicates: 0 Warnings: 0

mysql> ALTER TABLE post ADD FULLTEXT (pagetext);
Query OK, 1762278 rows affected (14 min 59.70 sec)
Records: 1762278 Duplicates: 0 Warnings: 0

mysql> OPTIMIZE TABLE post;
+----------+----------+----------+----------+
| Table | Op | Msg_type | Msg_text |
+----------+----------+----------+----------+
| vb3.post | optimize | status | OK |
+----------+----------+----------+----------+
1 row in set (1 min 21.15 sec)

[cerebro]

mmm same problem whit server load...
Treads: 112,110, Post: 1,745,876, Members: 29,111

First day work fine whit 400 users online, second day server go to 8...11.11!!!!!!

i have to uninstall...some fix?

[cuerty]

It's great, mysql load average goes as down as it can ;-) Thanks.

[Erwin]

I've installed this!

It's working well... I'll look over the server loads over the next few days and report on it.

Overall, took me only 3 hours... most of the time it was trying to find out why I couldn't add a fulltext index to post, to find out that my secured tmp directory was too small, so I had to modify my.cnf to change the tmpdir temporarily.

[JohnWoo]

Sorry for disappearing
Returning to discussion about SQL requests with or without LEFT JOIN
I recently had a chance to do a lot of tests on one large programming forum with about 900.000 posts. And yes - it is true that excluding some forums from searches do not make search run faster... But including left join don't make it perceptible slower
But after removing revelance it runs up to 10 times faster!
Here are results as requests and time in seconds below. Each request was executed 10 times with clearing DB cache after each. Fastest and slowest time below. Forums were not closed at that moment and there were about 120 online users. Think that numbers too different because of it

============
SELECT postid
FROM post AS post
LEFT JOIN thread AS thread ON post.threadid = thread.threadid
WHERE MATCH (
pagetext
)
AGAINST (
'+user +acces'
IN BOOLEAN
MODE 
)
LIMIT 0 , 200

0.0277 -  0.3782 s

============

excluding security forums

============
SELECT postid
FROM post AS post
LEFT JOIN thread AS thread ON post.threadid = thread.threadid
WHERE MATCH (
pagetext
)
AGAINST (
'+user +acces'
IN BOOLEAN
MODE 
) AND thread.forumid NOT IN (57, 64)
LIMIT 0 , 200

0.0197 - 0.2272 s
============

============
SELECT postid
FROM post AS post
WHERE MATCH (
pagetext
)
AGAINST (
'+user +acces*'
IN BOOLEAN
MODE 
)
LIMIT 0 , 200

0.0144 - 0.1043 s
============

============
SELECT postid, 
MATCH (
pagetext
)
AGAINST (
'user acces'
) AS relevance
FROM post AS post
LEFT JOIN thread AS thread ON post.threadid = thread.threadid
WHERE MATCH (
pagetext
)
AGAINST (
'+user +acces*'
IN BOOLEAN
MODE 
) AND thread.forumid NOT 
IN ( 57, 64 ) 
LIMIT 0 , 200

0.6938 - 1.3414 s
============

============
SELECT postid, 
MATCH (
pagetext
)
AGAINST (
'user acces'
) AS relevance
FROM post
WHERE MATCH (
pagetext
)
AGAINST (
'+user +acces*'
IN BOOLEAN
MODE 
)
LIMIT 0 , 200

0.5509 - 1.4414 s
============

[Erwin]

Well, my current server loads seem a bit lower than before I installed the hack. But it's not peak time yet. Search appears to be noticeably faster.

John, just tell me which code to use that is the fastest and puts the least amount of stress on the server, and I'll use it. Do I just remove the RELEVANCE part in search.php?

[JohnWoo]

yes to remove revelance replace

//fulltext search query
	$fulltext_sql = "SELECT postid,MATCH (".$what_field.") AGAINST ('".$norm_query."') as relevance FROM ".TABLE_PREFIX."post AS post LEFT JOIN ".TABLE_PREFIX."thread AS thread ON post.threadid=thread.threadid WHERE MATCH (".$what_field.") AGAINST ('$query'".iif($vboptions['allowwildcards'] ==1 && preg_match("~[\"\-\*+]~i", $query), ' IN BOOLEAN MODE', '').")".$limit_conditions." LIMIT 0, ".$vboptions['maxresults'];

with

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

and remove string

$postscores[$thispost["postid"]] = $thispost["relevance"] * $vboptions['multimatchscore'];

[cerebro]

Thanks again JohnWoo...just modify that in my file....in a few days will see