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

[motorhaven]

My database server has had no increase in load with the hack installed. 1.5 mil posts and 1000-1500 users online. My front end server is my concern, its been a dog since the day I upgraded from VB2 to VB3.

[msimplay]

Originally Posted by motorhaven

My database server load has had no increase in load with the hack installed. 1.5 mil posts and 1000-1500 users online. My front end server is my concern, its been a dog since the day I upgraded from VB2 to VB3.

hmm i thort i was the only one that noticed vb3 seems more server intensive then 2

[ImportPassion]

motorhaven: you think ur is bad, i run always around 5 prolly and get up to 20 alot. I don't have the $$ to get a sep db server or http server. just running dual xeon 2.8 2gb ram. still on vb2 tho with too many hacks.

[kmike]

Database server was not issue with vb2 and is not issue with vb3 - db server load rarely reaches 2.0, though we didn't see whole 1300+ users online yet with vb3. Will see how it goes on Monday.
When I wrote about server load 30, I meant frontend server - http processes were stacking up on locked mysql queries, causing peaks of load.

With search disabled, frontend server is humming nicely with average load around 1-2. Maybe a little higher than with vb2 at the same day/time, but acceptable.

[motorhaven]

Our front end server had no increase in load since adding fulltext. Its just as crappy after fulltext as it was before! Here's a portion of my.cnf:

max_connections = 200
key_buffer_size = 496M
# myisam_sort_buffer_size is used when repairing tables only.
#myisam_sort_buffer_size = 48M
join_buffer_size = 2M
read_buffer_size = 2M
read_rnd_buffer_size = 2M;
sort_buffer_size = 4M
table_cache = 1536
thread_cache_size = 250
wait_timeout = 3000
connect_timeout = 60
max_allowed_packet = 8M
max_connect_errors = 10
query_cache_limit = 2M
query_cache_size = 24M
query_cache_type = 1
thread_concurrency = 8
# Full text search fine tuning
ft_min_word_len=3
ft_max_word_len=25
ft_stopword_file=/home/database/mysql/stopwords
flush_time=86400

3 gigs of RAM on the server.... every bit of the database is either in MySQL's buffers or the Linux disk cache.

ft_max_word_len=25 really helped to decrease the size of the post index file. It specifies the maximum word length to index. There aren't very many useful words longer than 25!

[Erwin]

Originally Posted by 7thgenCivic.Com

motorhaven: you think ur is bad, i run always around 5 prolly and get up to 20 alot. I don't have the $$ to get a sep db server or http server. just running dual xeon 2.8 2gb ram. still on vb2 tho with too many hacks.

Upgrade to 4 Gb of RAM - that would help a lot.

[kmike]

Originally Posted by Erwin

Upgrade to 4 Gb of RAM - that would help a lot.

I guess it is offtopic, but irony aside, our 2G RAM frontend server really could use some RAM upgrade. It started swapping after upgrade to vb3. It had a measly 10-15Mb swap size allocated with vb2, but now it is nearing 500Mb.

[motorhaven]

Here's an idea for those with disk space (and index RAM to spare)...

What about 2 post tables? One without the fulltext index, and the other with it.

Searches would be pulled from the secondary post table with the fulltext index which would eliminate locking issues with the primary post table. Of course this would require every insert, update and delete to the post table to be duplicated to the secondary table.

I'm beginning to suspect the post table lock mentioned may, as noted previously, cause lock issues causing our front end server to sit in an I/O wait for the data. This would explain why the front end server sees a large load and the database doesn't.

I increased the key buffer size on the database server from 500 meg to 1 gigabyte and the query cache to 48 megabytes and saw no real difference on the database server but did see a load decrease on the front end server!

Just a thought.....

[kmike]

Having 2 post tables won't help if they're updated simultaneously - locking just shifts from first to second post table. Now, if new posts/edits are queued and dropped into second table in batches (via INSERT DELAYED or UPDATE LOW PRIORITY), it would make more sense and indeed will eliminate most of locking.

[kmike]

Originally Posted by JohnWoo

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)

Not so.
Typical limiting condition consists of something like:
thread.forumid [NOT] IN(25,197,68,159,193,191,120)
sometimes with posts.userid IN (100,200,300) added.

Now, if you run "EXPLAIN" on resulting queries, you'll see that first index used by mysql is FULLTEXT index on pagetext, and only then threadid index is applied using "where". It means ALL posts are being scanned first using FULLTEXT. The only search type where that LEFT JOIN really limits number of posts to search is a search within thread - EXPLAIN shows that first used index is threadid, and only then fulltext index on pagetext is used.
But... I suspect mysql has a bug here, though - actual search time is exactly the same as without "AND thread.threadid=nnn" condition in WHERE clause, which suggests that fulltext index is used here first anyway.

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

Fulltext hack for VB2 always used boolean mode search, so number of results was much much lower.
Also, now that vb3 has a logic for caching search results, querying all posts and limiting results afterwards depending on user's permission or search preferences suddenly appears quite logical, isn't it? Chances are that another user will run search with the same query, in that case we'll just pick up saved post ids and apply query logic filter to them.