Howto : Enable FullText Searching in vBulletin 3.0.2/3
 

This feature is not offcialy supported at this time, so enabling this is like hacking your forums :)

You'll need to shut your boards down for security reasons and enter debug mode, or enable debug mode for yourself only.

Once done, go into the vBulletin AdminCP > and select vBulletin Options.

You will see an area called Version Info and Other Untouchables
At the bottom you will see

Fulltext Search

Run the two querys it says to, adjust for table prefixes.

Then click on yes and hit save.

This feature is not offcialy supported at this time, so enabling this is like hacking your forums :)

Thanks for pointing this out, Zach :) Much appreciated.

This is really GREAT news!

Any idea how stable/reliable the code is already and when this will become "officially" available?

No clue about stability, you NEED to be on mysql 4.0.2+ at least, and even if i did, and i really dont, i couldn't say.

thank you very much Zach!!

I'm running it on my forums and I haven't found any problems yet.

We are using mySQL 4.0.20, so this shouldn't be a problem as fulltext search also works in a custom addon :)

I think i'll make a dump of the production database and just play around a bit with this.

I did a little experimenting with queries and came up with the following.

Running this should turn fulltext on:

Running this should turn fulltext off:

Edit: By the way, I recommend running these queries one at a time or in PHPMyAdmin.

Another Edit: I just fixed a careless mistake that didn't index pagetext. ^_^;;

3rd Edit: By the way, if you run those queries, you will want to go to vBulletin Options in the control panel and click "save." Otherwise the cached copy of $vboptions will contain the old settings.

Moved to modifications hints and tips - cheers zach :)

I somewhat doubt that these queries are correct - they only index titles, but not the post itself (pagetext) ;)

Oops. ^_^;; I'll fix that in a moment.

Edit: Fixed.

You do need to update the datastore too Mr Shining Arcanine, silly pokemon :)

Sorry to be stupid but where do you enter debug mode?

http://www.vbulletin.com/forum/showt...182#post594182
number 7 :)

Thank you, Zachery :D

Doesn't that update itself?

Nope, someone must actualy force it to update, or run a query to update it.

The datastore will only be updated if you save that part of it

So after running that query you would need to go into the ACP and save your settings.

After spending an hour looking over vBulletin's source code (for the first time). I found out exactly why you didn't just post a list of queries... the datastore contains a serialized version of $vboptions. :disappointed: As they say, live and learn. :)

I just did the fulltext upgrade and then went to Update Counters and cleared the search index as it's no longer needed. Is that all I have to do or do I have to do something else to get rid of the old search index?

There is also the word table.

I checked my db in phpmyadmin and it seems like my word table is already blank (it's only 1kb and it has nothing to browse). Thanks!

I know how to update the datastore, its just simpiler this way :p

just for clarity, if i make the mod, will i still have to build my index's or will it be done automaticall?

When you execute the queries to add the indexes to thread and post table mySQL will built them automatically.

Do we run these in addition to turning this on? And how do we update the datastore?

Bob, its best to it as my instructions in the first post instead of quersy.

:p

a query to empty that value in datastore, the value should be rebuilt itself :D, However, it's still safer to use ACP :D


I can see that this piece of beautiful work is working but not perfectly :)
you still have to improve it.

I did few quick test with UTF-8, and it was able to search fine, but the Highlight didnt do the job properly. It didnt highlight some special characters.

Activated it. So far not a single problem. Let's cross fingers. Should it improve performance, apart from saving some space?

So shall we drop the 'word' table? as it's no longer needed?

This would be nice if someone would link to it from the vb.com discussion thread, a lot of people have asked and they said to look here... yet it took me quite a few search trys to find this thread (how ironic)... lol

Yes. For big sites with lots of posts the normal search function is unusable as it requires way too much RAM.
On my board for example (postindex = 25 million rows) I get memory limit exceeded errors from PHP with specific keywords, even if I increase the limit > 64 MB.
So it is impossible to search for those keywords :(

With mySQL fulltext search this shouldn't be a problem any longer.

The vB 3.0.2 mysql fulltext search is still not as efficient as the vB3 hack version (with my own customizations to the code). I tried the 3.0.2 version, and with over 8 million posts, it still brought the server down. With the other hack version, my server has not problems. :) Just making an observation.

and what pray tell is that? Something that offers no options?

Well, I tried the default vB3 fulltext search, and it scanned almost 1,000,000 rows for the word "test" for over 300 seconds before it borked. Never managed to complete it.

With the custom code, the word "test" took 19 seconds (which is not fast by any means but it's a big database) - and scanned 100 rows (which is a limit I imposed, but which I can increase). This is just using the normal search page, with all settings default, searching all forums.

It's not a fault to do with vB3 at all - fulltext has its own limitations too.

The day that InnoDB supports fulltext, is the day the post table goes InnoDB, to prevent table locking.

Well that is the reason then, we are not imposing a limit on the results as of yet. We are going to look at doing that coming up.

Here is one optimization. As I have time to work on this for 3.1, I'll try to post changes for you.

With 3.0.2 or 3.0.3 you should edit search.php and look for this around line 1303:

FROM " . TABLE_PREFIX . "post AS post

Change this to:

FROM " . TABLE_PREFIX . "post AS post " . iif($vboptions['fulltextsearch'] AND $searchuser, "USE INDEX (userid)") . "

When searching by posts by a specific user and returning results as posts, this will force mysql to search based on userid rather than using the fulltext index. On the whole this will be faster than searching fulltext and then manually scanning for userids.

I'm just getting into imposing the proper limit options and re-evaluating the relevancy junk for the non-fulltext search. It is the extra queries that we have in place to support this pseudo-relevancy that complicates limiting searches when returning results as threads.

Freddy, I have activated the full text search but, apparently, it's making things worse, performance wise, for us, with huge load spikes that I am tracing back to the search function, as deactivating it has instantly eliminated them.

Could this be possible? Forum address is forum.gamesradar.it

I followed Zach's instructions to the letter, however I'm finding that there are many words that are coming up with empty results.


For example, if I search for the word "from", I get no matches. I emptied out most of the "bad words" in searchwords.php, but that didn't do anything.


At the moment, Full Text Searching is missing the ball on quite a bit. Is there something else I need to do after enabling it? Some type of update or refresh to MySQL, perhaps?

As an additional note on my problem, most of the words I'm having problems with are either words less than four characters in length, or common words.


Now, my AdminCP has Minimum Word Length set to 3, and I've emptied most the stuff in searchwords.php.

Does the Full Text Search have a different set of options for it than the Regular Search?

If so, where are they, and what do I need to do in order to edit them?

Full Text search goes on MySQL's settings i believe, they have a list of common words and the mininum chracter limit is 4 i believe