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]

Upgraded. Thanks a lot! Searches are definitely FASTER than before.

I'd vote for this hack to be Hack of the Month, no problems. It's something that vBulletin should really have as default.

[motorhaven]

I've put full text search back in. Works fine. Turns out a lot of the performance issues on the site were crawlers from China/Korea. I've completely blocked both countries from the server and load dropped in half during prime time. Because my site is about trucks, virtually no one from those countries is a legit visitor, and we don't ship to them so its not a loss if a handful can't visit. Until I can come up with a better long term solution blocking is the best route (these crawlers don't obey robots.txt).

Also, I'm in the stages of finishing out a "final" solution (for now) to performance issues related to search and the post table. Basically, every time a post is added, edited, moved or deleted, that post information is changed in a secondary table (postsearch). Searches are conducted in this table, all other activity is in the primary table. Off course, this means two tables are updated/locked every time there is a post/edit/move/delete but the number of these per day is minor compared to the number of table locks during load. Initial tests look very good, just have to make sure there are no bugs.

[kmike]

We're running a cross-breed between this hack and fulltext search hack for vb2 (boolean queries only, no LEFT JOIN on thread table) for more than 2 weeks now. Runs smoothly and without any problems so far.

I think the total failure of this hack on our board is due to the fact that we have about 350 new threads per day, most of them happen in daytime. And mysql slow query log shows that some search queries take up to 30-40 seconds to complete. According to stats, there're about 15 of such slow queries per hour, likewise, with daytime peak. Couple that with LEFT JOIN on thread table, and you'll get quite a high chance of locked mysql process for new thread creation for every such search. What's worse, I think select queries will be locked as well as they wait in line for INSERT process to complete (captured mysql process list during lockup proves this).

I'm not sure why fulltext searches do not lock post table as badly - new posts should trigger post table locks much more often than thread table gets locked with new threads. Maybe there's some kind of row-level or page-level locking kicking in here?

motorhaven:
I see the benefit of your solution in that only search queries will be locked, instead of hundreds of common SELECT queries waiting for INSERT/UPDATE to complete. Like I said before, you can improve your hack by deferring new posts or updates to second table and executing them in batch, probably using cron job like the one which is updating deferred thread views in official vb3.

[Erwin]

kmike, care to share your no LEFT JOIN version?

My slow_queries log shows that LEFT JOIN is a main culprit of queries >100 seconds.

[kmike]

Erwin: well, I have to tidy up and clean the code from some custom tweaks first. Also, my hack changes search template a bit, like vb2 version of this hack did - there're 4 search fields instead of just one:
Search for items including these words:
and excluding these words:
and optionally including these words:
search for exact phrase:

That said, are you sure your search queries would run faster without LEFT JOIN? From my tests, that LEFT JOIN doesn't add substantial processing time, it only causes thread table locking which in turn causes peaks of load on frontend server.

[Erwin]

I have only 2 queries showing up in my mysql slow_queries log - the LEFT JOIN in search.php for this hack is one of them. The other one cannot be help. I have a lot of threads on my forum.

Please do share your code. Anything to remove that slow_query.

Another thing, can you also share the IPs that you've banned? I suspect I have the same problem too.

[kmike]

Erwin: ok, I'll post my changes after tidying up. Not before the end of next week though - I will be out of reach of computer until then.
Also, I want to point out that you won't eliminate that slow query altogether. I also have that search related slow query in mysql log, though its probably not so bad as for you - 20-40 seconds at max. It is limitation of mysql fulltext search engine, the more posts you have in your db the longer search proceeds. We can't help it either, just wait for mysql folks to improve their engine.

[Erwin]

It's not too bad at the moment - ranges from 30-60 seconds for the LEFT JOIN query.

Thanks for sharing your code. I want to test it out and see if it make a difference.

[Erwin]

Aarggh... just had another server crash because of the LEFT JOIN query in search.php - it locked up the whole mysql server, and the query was up to 400,000 rows before I nuked it.

[msimplay]

Originally Posted by Erwin

Aarggh... just had another server crash because of the LEFT JOIN query in search.php - it locked up the whole mysql server, and the query was up to 400,000 rows before I nuked it.

keep us posted on your advanced with the leftjoin thing
ps can you search within threads using your version of this hack ?

because when i did it seemed to search outside of the thread in question aswell