Howto : Enable FullText Searching in vBulletin 3.0.2/3

[Zachery]

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 :)

[Daniel P]

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.

[Andreas]

This is really GREAT news!

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

[Zachery]

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.

[muse.mu]

thank you very much Zach!!

[ShiningArcanine]

This is really GREAT news!

Any idea how stable/reliable the code is already and when this will become "officially" available?
I'm running it on my forums and I haven't found any problems yet.

[Andreas]

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.

[ShiningArcanine]

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

Running this should turn fulltext on:

ALTER TABLE post ADD FULLTEXT INDEX (title,pagetext);
ALTER TABLE thread ADD FULLTEXT INDEX (title);
UPDATE `setting` SET `value` = '1' WHERE `varname` = 'fulltextsearch' LIMIT 1 ;

Running this should turn fulltext off:

UPDATE `setting` SET `value` = '0' WHERE `varname` = 'fulltextsearch' LIMIT 1 ;
ALTER TABLE `post` DROP INDEX `title` ;
ALTER TABLE `thread` DROP INDEX `title`;

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.

[Dean C]

Moved to modifications hints and tips - cheers zach :)

[Andreas]

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

[ShiningArcanine]

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.

[Zachery]

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

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

[Tailfeathers]

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

[Zachery]

http://www.vbulletin.com/forum/showthread.php?p=594182#post594182
number 7 :)

[Tailfeathers]

Thank you, Zachery :D

[ShiningArcanine]

You do need to update the datastore too Mr Shining Arcanine, silly pokemon :)
Doesn't that update itself?

[Zachery]

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.

[ShiningArcanine]

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. :)

[Tailfeathers]

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?

[Freddie Bingham]

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.

[Tailfeathers]

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!

[Zachery]

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 know how to update the datastore, its just simpiler this way :p

[sabret00the]

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

[Andreas]

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

[Boofo]

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

Running this should turn fulltext on:

ALTER TABLE post ADD FULLTEXT INDEX (title,pagetext);
ALTER TABLE thread ADD FULLTEXT INDEX (title);
UPDATE `setting` SET `value` = '1' WHERE `varname` = 'fulltextsearch' LIMIT 1 ;

Running this should turn fulltext off:

UPDATE `setting` SET `value` = '0' WHERE `varname` = 'fulltextsearch' LIMIT 1 ;
ALTER TABLE `post` DROP INDEX `title` ;
ALTER TABLE `thread` DROP INDEX `title`;

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.
Do we run these in addition to turning this on? And how do we update the datastore?

[Zachery]

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.

[mtha]

I know how to update the datastore, its just simpiler this way :p: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.

[gorman]

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

[adhari_com]

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

[eoc_Jason]

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

[Andreas]

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]

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]

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]

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]

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]

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

[Ocean]

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




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


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

[Ocean]

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:




ft_min_word_len=2
ft_stopword_file=""




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!

[ConqSoft]

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?

[Erwin]

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.
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.

[Ocean]

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?



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... :)

[gorman]

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.hmmm... as a temporary solution, do you think it will improve performance to forbid searching for threads and limiting the search options to posts?

[ConqSoft]

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.

[CaptChill]

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';

[Ocean]

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. :)

[Zachery]

Im confused....

[Nitesh]

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.

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.

[Ocean]

Im confused....



Zachery, you had made a post here: http://www.vbulletin.com/forum/showpost.php?p=594182&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. :)

[Freddie Bingham]

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.
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.

[buro9]

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 ;)

[dustyb]

This feature is not offcialy supported at this time, so enabling this is like hacking your forums :)[/color][/size]

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


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?

[Zachery]

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 ;)

[gorman]

We realize that this search is not the answer for larger forums and that is why it is unsupported. 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.

[Erwin]

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.
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.

[gorman]

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...

[dustyb]

You also need to enable debug mode ;)

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?

[ShiningArcanine]

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.

[dustyb]

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.

[Erwin]

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...
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).

[M1th]

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...
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. :(

[buro9]

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.

[Nitesh]

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 :)

[Zachery]

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.
If you did the fulltext system you didnt need to reindex, mysql did that on its own

[welo]

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?

[Zachery]

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.

[MrToasty]

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

[ZiRu$]

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!

[MrToasty]

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...

[AnhTuanCool]

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?

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

[Zachery]

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.

[AnhTuanCool]

Oh yah, I see it, thanks bro ;)

[scotty]

The day that InnoDB supports fulltext, is the day the post table goes InnoDB, to prevent table locking.
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?

[Zachery]

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 :)

[Killsparer]

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).

[Smitty]

The vB 3.0.2 mysql fulltext search is still not as efficient as the vB3 hack version.Where is the 'hack' version? I did a search here and didn't spot it.

Does it require mysql 4.0.2+?

[Zachery]

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

[buro9]

Is it me or does the "Find In Thread" functionality fail with this turned on? Is there a quick fix to this?

I've merged boards and fulltext searching means that I won't have to rebuild search indexes on a 300,000+ post forum.

I'm merging again soon to go over the 500,000+ post mark, and I'll have the same request then too ;)

[mickmel]

I'm on version 3.0.3, with debug mode on, but there's no "full text" option in the "version info and other untouchables" section (or anywhere else, for that matter). Did they move (or remove) the fulltext option in 3.0.3?

[buro9]

I'm on version 3.0.3, with debug mode on, but there's no "full text" option in the "version info and other untouchables" section (or anywhere else, for that matter). Did they move (or remove) the fulltext option in 3.0.3?
You're looking in the AdminCp right? Under vBulletin Options?

[mickmel]

You're looking in the AdminCp right? Under vBulletin Options?

Yep. Admin CP --> vBulletin Options --> Version Info and Other Untouchables

The options in there are:
-vBulletin Version Number
-Save Attachments as Files
-File Attachment Path
-Save Avatars as Files
-Avatar File Path
-Avatar URL
-WYSIWYG Use DIV not P
-BB Code Parser Function

Then the [save] and [reset] buttons, and the debug box below that.

Am I just missed something dumb here? :)

[mickmel]

I've looked everywhere (both in my AdminCP and online for answers) and can't find this option. Anyone have an idea where it might be?

[M1th]

I've looked everywhere (both in my AdminCP and online for answers) and can't find this option. Anyone have an idea where it might be?
As stated in the first post:

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

So...


$debug = 1;

Add that in your config.php file.

[mickmel]

As stated in the first post:

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

So...


$debug = 1;

Add that in your config.php file.

Right. I did all of that. Then I go to the Admin CP, then to vBulletin Options, and the Version Info and Other Untouchables option is there. I go into that, but don't see an option for fulltext. All I see are:
-vBulletin Version Number
-Save Attachments as Files
-File Attachment Path
-Save Avatars as Files
-Avatar File Path
-Avatar URL
-WYSIWYG Use DIV not P
-BB Code Parser Function

I'm not sure why it's not there. Is it because I'm on 3.0.3 and not 3.0.2?

[Zachery]

Right. I did all of that. Then I go to the Admin CP, then to vBulletin Options, and the Version Info and Other Untouchables option is there. I go into that, but don't see an option for fulltext. All I see are:
-vBulletin Version Number
-Save Attachments as Files
-File Attachment Path
-Save Avatars as Files
-Avatar File Path
-Avatar URL
-WYSIWYG Use DIV not P
-BB Code Parser Function

I'm not sure why it's not there. Is it because I'm on 3.0.3 and not 3.0.2?
Its there in my 3.0.3... how did you upgrade?

[mickmel]

Its there in my 3.0.3... how did you upgrade?

Nothing out of the ordinary. I've been slowly upgrading since we started back on version 1.something. Do you know what file I might be out of date on and I'll make sure to reupload that correct one?

[mickmel]

I've re-uploaded all of the files in the "admincp" directory, but I still don't have the fulltext option in there. Where is a setting like that stored?

[Zachery]

I've re-uploaded all of the files in the "admincp" directory, but I still don't have the fulltext option in there. Where is a setting like that stored?
When you upgraded you made sure all the files were 3.0.3 and ran the upgrade scripts? If you dont see that option its telling me that the new vboptions.xml didnt get uploaded before you finished the upgrade.

[mickmel]

When you upgraded you made sure all the files were 3.0.3 and ran the upgrade scripts? If you dont see that option its telling me that the new vboptions.xml didnt get uploaded before you finished the upgrade.

I'm not sure what happened. The last few upgrades went successfully. The problem was a missing entry in the options table (I think that's the name). Anyhow, it's working now.

Next problem... :)

I'm having problems getting one of the ALTER commands to work. After failing with the "post" one a few times, I tried the "thread" one. It completed successfully in just over 4 seconds. I'm running these queries via SSH.

I've run the "post" ALTER command a few times and it's never able to finish. I've repaired the table (no errors), optimized the table and flushed the index. It still won't finish. I might just be impatient, but I let it go for an hour and it hadn't finished. No error message, just no result. The thread table has about 78,000 records in it, the post table has about 1.3 million records in it.

The other strange thing is that my server load while it's working is almost nothing. It's at most 0.05. I would expect that if it was working that hard on the post index it would be putting more strain on the server.

So, the questions:

- Is a one hour wait for this a problem or am I just impatient? Given that the thread table takes 4 seconds and the post table has about 15 times more records, it should only take about 60 seconds, right?
- Is there a way to simplify the query into a few separate ones so that I can run them one at a time?
- Anything else I can do to optimize the table so this might run faster or finish?

Thanks,
Mickey

[Zachery]

ALTER commands usualy take a while, and I am not running this on my own board anymore so i cannot comment too much on it, I hope someone who has abit more knowleged about this can respond.

[mickmel]

In the hopes of speeding it up, I've cut the post ALTER command down a bit. Instead of:

ALTER TABLE post ADD FULLTEXT INDEX (title,pagetext)

I'm just doing

ALTER TABLE post ADD FULLTEXT INDEX (title)

Assuming it works, I'll do the pagetext as a separate query as well. Right now we're at 40 minutes and no sign of finishing.

The only reason I'm concerned about it taking so long is that the thread table completed so quickly.

I'll let it go a while longer and see what happens...

Mickey

[Zachery]

The post talbe is quite abit larger... have you configured mysql's setting since installing mysql? an unoptmized my.cnf/ini can be horrid for running larger querys like the alter querys.

[mickmel]

The post talbe is quite abit larger... have you configured mysql's setting since installing mysql? an unoptmized my.cnf/ini can be horrid for running larger querys like the alter querys.

Yep. It's optimized and it's a fast server (dual Xeon, 2 gigs RAM, SCSI). We're at 2 hours now and it'll still working on it. This can't be right...

[mickmel]

Ok, I did a bit more digging and I might have found part of the problem. I was looking at the table in PHPMyAdmin, and found that it has a ton of indexes on it. Here is what it has...


Keyname Type Cardinality Action Field
PRIMARY PRIMARY 1526312 Drop Edit postid
dateline INDEX 1526312 Drop Edit dateline
iconid INDEX 15 Drop Edit iconid
userid INDEX 11924 Drop Edit userid
threadid INDEX 508770 Drop Edit threadid
userid
title FULLTEXT 508770 Drop Edit title
pagetext
title_2 FULLTEXT 1526312 Drop Edit title
pagetext
title_3 FULLTEXT 508770 Drop Edit title
pagetext
title_4 FULLTEXT 1526312 Drop Edit title
pagetext
title_5 FULLTEXT 508770 Drop Edit title
pagetext
title_6 FULLTEXT 1526312 Drop Edit title
pagetext
title_7 FULLTEXT 508770 Drop Edit title


There are 1,526,312 rows in that table, so the ones that show that appear to be done. Should I drop all of the title indexes except for one that has all of the records (like title_2)? Or should I drop all of them and try to reindex?

[Killsparer]

Is it me or does the "Find In Thread" functionality fail with this turned on? Is there a quick fix to this?

Same problem. Any way to fix this?

[buro9]

Same problem. Any way to fix this?
This is now all working in vb 3.0.5 :D

Congrats to all for their hard work, it is VERY appreciated. :)

[buro9]

Does anyone know... are Similar Threads driven by the fulltext search too?

i.e. If I purge my word index tables, will I break anything now that most of this appears to be working?

[Smitty]

Good question.

What is the status on this? Are a lot of you using it successfully? I'm considering trying it.

[mickmel]

Are a lot of you using it successfully? I'm considering trying it.

I've been using it on my largest site (1.6M posts) for a few months and it's been working very well.

[Freddie Bingham]

I've been using it on my largest site (1.6M posts) for afew months and it's been working very well.Personally, Iwouldn't recommend the 3.0.x fulltext search for large sites. Functionally, it works, but since it only used In Boolean mode, it canbe resource heavy. The 3.1 FullText search has options for Bololean andNatural Language search as well as many little optimizations throughout the code.

[neocorteqz]

You know , I've seen that option in the vB options in the admin CP. wanted to try it, but wasn't sure if I should. Now can I test it out woithout clearing my search index?? I'd rather not reindex the search index if this doesn't turn out good results.

[Smitty]

Thanks for the tip, Freddie. I'll have to read through the thread again so I better understand - You mean vB 3.0.x vs. a future 3.1 version of vBulletin?

My board isn't very big. The data and index of the database is only about 200 megabytes (attachments are in a separate directory).

I don't plan on doing this right now - maybe in a couple of weeks. Just scoping things out in advance.

[nawialkair]

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

How do I enter the debug mode??

THanks

[Marco van Herwaarden]

Edit your config.php, and place the following line in it:
$debug = 1;

[LambHyjoo]

Hi, I just switched to FullText Search with Vbulletin 3.0.13

I would like to know if the FullText Search is know more stable than it was in the past ? Is it better than the FullText Hack provided by JohnWoo (https://vborg.vbsupport.ru/showthread.php?t=62282&highlight=fulltext)

Last question : Except the two queries (Index stuffs with Post and Thread tables) and the "vboption[fulltext] = 1", am I supposed to do something else ?

Thanks in advance for your answears !

/Edit : One more question :D The Relevance Search doesn't seem to work with Fulltext ?