Well, I need to bypass/change these settings. What do I need to do in order to accomplish that?


(Thanks, by the way...) :)

Aha! Okay, I figured out how to change/bypass the MySQL settings for Minimum Word Length and the Stopword lists.


If anyone else is interested, here's what you have to do:

NOTE: The following instructions are applicable for MySQL 4.0.18. I believe they are valid for 4.0.3 and above.



1. In your MySQL option file ("C:\Windows\My.ini", for example), under the central group category ( "[mysqld]", for example), add the following two items:



The first item sets the minimum word length to be indexed. In my example, I have it set to 2 characters.

The second item bypasses the MySQL Stopword list - which covers common words like "from", "and", etc...


Alternately, you can always edit the Stopword file directly. To do this, you need to find out what the default file/location is for your particular install (based on version, Platform, how it was installed, etc...)

Use and/or edit either of these two options to suit your needs.


2. After you have made the above changes, restart MySQL. (or, alternately, the entire server).

3. From your AdminCP, execute the following Queries:

REPAIR TABLE post QUICK
REPAIR TABLE thread QUICK


If you use Table Prefixes, edit "post" and "thread" accordingly.



After that, you should be done!

I did this, and cleared the search index, but it's still showing:

Index Usage 50.31 MB

in the AdminCP stats. Should that be zero?

FWIW, I've found removing the relevance checks speeds up the search, and the quality of the search is not really affected.

Also, like you say, it's really the returning the results as threads that's the site killer for large forums even with optimization and fulltext. Returning results as posts is not an issue with the right search code.

I'll give you my best guess, which is that it should NOT be zero. When you enable FullText Searching, the two queries that you run create indexes within your "posts" and "threads" tables. Although these are fulltext type indexes, I would think that by virtue of being part of your database, they will still show up as part of your Index useage.

One of the Devs might be able to chime in to say whether I'm way off the mark or not... :)

hmmm... as a temporary solution, do you think it will improve performance to forbid searching for threads and limiting the search options to posts?

With Full Text on, go to a thread (any thread), use the Search This Thread option and search for something that appears in the current thread.

When I did this, I also got results from other threads.

Which PHP form do I look for Zach?

Q7. How to I turn on debug mode?

A7. Find

PHP Code:
// Prefix that all vBulletin cookies will have
// For example
$cookieprefix = 'bb';

add under

PHP Code:
// Enable / Disable Debug mode
// 1 on. 0 off.
$debug = '1';

It's true that that very important piece of information is missing from the post in question. :)

But to answer it for you, it's includes/config.php. :)

Im confused....

I agree with you 100%, with the vbulletin fulltext search it just used to crash the server. Re-installed the hack and it works perfectly now :) but I think this only affects the larger forums.

Zachery, you had made a post here: http://www.vbulletin.com/forum/showp...82&postcount=1


... in which you answered the question "How do I turn on Debug Mode". The problem is that you never stated where those code changes had to be made. You never mentioned which file/template you were referring to. :)

We realize that this search is not the answer for larger forums and that is why it is unsupported. We don't want to encourage users to enable this in hopes it will solve all of their problems. It is simply a test to show that we can return the same results that we would return when using our postindex. If you compare the results with fulltext turned on and off, you will see they are very close. You should also compare the search results of any hacks that are perceived to be very fast to this. I'm confident that you will find that those results may very greatly as some things must be sacrificed in order to achieve speed.

This is where I have an ambitious plan to create a dynamic search system that can learn what the bad things to search for are and use less intensive queries to return less than perfect results.

So if I'm on a music forum and searching for The The this would be bad ;)

Hate to be the thread idiot, but for the life of me, I do not see any Section in the vBulletin Options entitled "Version Info and other Untouchables". I downloaded and upgraded to 3.03 this afternoon. Has there been a change in options.php today?

You also need to enable debug mode ;)

I'm sorry to read this now. I know you stated it was unsupported, but I'd rather have had this specific info (not good for larger forums) before activating it and then being forced to disactivate it and resort to Google search, while waiting for further optimizations. Silly me. Not your fault or anything.

What you say is true - precision and accuracy in searching is sacrificed the more you optimize the search by removing relevancy and other things like that.

However, personally, and my forum members would agree, I would rather have a relatively inaccurate search engine and a site that actually remains online and runs fast, than a great search engine, but one which hangs the site and causes apache and mysql to crash. :) A poor search engine is better than no search engine at all. So far, my members haven't noticed anything different - and this is with just serving them 100 results.

The truth is though, is that this is a limitation of MySQL and MyISAM/ InnoDB tables, and not an intrinsic problem with vBulletin, which is greatly optimized as it is. I do appreciate Jelsoft working on releasing a fulltext search early, when they didn't have to. Good job with that.

I am waiting the day I can have fulltext search and row-level locking together - at the moment they are mutually exclusive with MyISAM.

Freddie... is it possible to have some quick fix to speed up the current full text search option? I'm available to test things, as, as of now, I was forced to disable and there's no way I'm gonna reindex everything under the old option (yes, I emptied the search table, call me stupid, I know I am...). 2.5 million posts board, with 250+ users online...

Duh, thanks. I indexed my database, enabled mysql full text, cleared the vB search index, dropped the word table, wrote in the quick hack for more optimized search by user name. All seems to be running smoothly (on a development server). I think resetting the mysql minimum word length from 4 down to at least 3 is going to be pretty standard procedure too.

But I guess the question begs... what's the benefit? I'm reading about some performance issues here on big boards. We have a small board (150K posts). I don't have the luxury of load testing this implementation, so it would be sort of trial by fire to launch it live. Are there any basic comparisons between vB search and mysql full text that I could measure right now? Query response times? Memory useage? Details?

If all of this just serves to reduce the size of the overall database, and eliminate the hassle of maintaining the cumbersome vb search index... and all other things from the user perspective remain equal... then I'm not so sure if it is really necessary at this point for us. Comments?

In my informal tests, fulltext is faster than the cumbersome vb search index.

Zachery, just a follow-up... after implementing this "hack" what is your recommendation for ACP settings in the "Message Searching Options" and "Message Searching Relevance" options areas? Are any of these settings relevant when running with full text search? Would it make sense to turn anything off or adjust anything there to achieve greater optimization? Also, I assume you recommend clearing the old vb seach index, and for the supremely confident dropping the vbword table? Thanks.

Ahhh... with 2.5 mil posts, reindexing would take a couple of days. :(

Maybe install the vB3 fulltext search hack - it does work, and you won't have to reindex, and still utilize the fulltext (since you already have fulltext indexes on the post table).

Ditto, I'd be great if we could have a quick fix for this. I did what gorman did with the search table and now I'm stuck. :(

I re-indexed!

But on my first go it escalated the load so high that my hosting company rebooted thinking something got out of hand.

I had to modify the admincp code so that the redirect paused 20 seconds... it took over 2 days to re-index all of my boards doing 250 posts with 20 second pauses.

What joy!

I think I'll stick with the old method until this is proven.

You dont need a quick fix or to re-index the search, just install the proper mysql search hack and that will sort everything out :)

If you did the fulltext system you didnt need to reindex, mysql did that on its own

Erm, Zachery? I haven't applied the 0.3 upgrade to my forum yet, but I did on a client's board and nowhere do I see in VB Options "Version Info and Other Untouchables". His is a totally unhacked board too (so far anyway). Any ideas?

You need to enable debug mode.

As mentioned earlier in the thread, the thread search doesn't seem to work with fulltext.. it simply retrieves results from the regular search...

I don't really understand this fulltext searching....does it only have to do with the members ability to search through the forums or does it allow the ADMIN to search for phrases under "Search Templates"?

Thanks!

Bug found - if a user searches for anything with one quotation mark in the query (i.e. 12"), I get an error... but if you use 2 quotation marks, as used with fulltext searching, it works fine...

Can anyone tell me where is that AdminCP stats at? :ermm:

You need to enable AdminCP Quick Stats in the vBulletin options under AdminCP Prefrecnes.

Oh yah, I see it, thanks bro ;)

does that mean, if i don't use the fulltext search, i can change the post table to InnoDB? and have no table locking any more?

IIRC Erwin found that InnoDB was slower than the current and has moved his other tables back, i may be wrong :)

Just wanted to say thanks for this HowTo-Guide!

Switching to FullText-Searching solved all my problems, like timeouts (when editing or deleting a posting/thread) and table-locking (while searching).

Where is the 'hack' version? I did a search here and didn't spot it.

Does it require mysql 4.0.2+?

Btoh versions require MysqL 4.0.X