Howto : Enable FullText Searching in vBulletin 3.0.2/3

[Andreas]

Originally Posted by gorman

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

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.

[Erwin]

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.

[Freddie Bingham]

Originally Posted by Erwin

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?

[Erwin]

Originally Posted by Freddie Bingham

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.

[Freddie Bingham]

Originally Posted by Erwin

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.

[Freddie Bingham]

Originally Posted by Freddie Bingham

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.

[gorman]

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

[Ocean]

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?

[Ocean]

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?

[Zachery]

Originally Posted by Ocean

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