Enabling 3-Character Searches in vBulletin 4.x

This post is in the category: Guides

Posts here are mostly step-by-step guides on how to replicate something I have set up in the past. Read over my About page to see how I show commands/output and read the disclaimer.

This is not as easy as flipping a switch. MySQL Full-Text Search is used by vB and has a minimum word length of 4 by default. This means that we have a system-level config to change and tables to rebuild – fun!

If you have a large forum, run this in a test environment first. (You do have a test environment, right?) As we are running database schema updates, corruption and downtime is always a risk.

Update the vB Setting

Login to your forums admin console. Navigate to Settings -> Options -> Message Searching Options. Change Search Index Minimum Word Length to 3.

Notice the verbiage in this section: “Note that MySQL Fulltext also has its own minimum word length that must be changed at the server level.” This is what we are going to change next.

Update the MySQL Setting

Restarting MySQL will cause a few seconds downtime.

Open up the MySQL config file. Your config file location may vary. RadHat is usually /etc/ and Debian is /etc/mysql/.

[root]$ vim /etc/my.cnf

Locate [mysqld], add this below: ft_min_word_len=3.

Restart MySQL. Note the service name may be mysqld.

[root]$ service mysql restart

Hopefully MySQL will restart after a few seconds. If the restart fails…PANIC!! Then open up the config file again and find the likely syntax error.

Rebuilding MySQL Table Index

This is a step that I am not 100% sure is required. I have found other how-to’s that mention rebuilding the posts and threads tables – but why? Neither of those tables have a full text index to be affected by this new setting, and vB searches query the search index tables, not the post table.

I have even come across other articles that suggest repairing all tables as “it won’t hurt”. Their database size must not be 10GB. I’m going to avoid that if I can, especially the post table.

From my own understanding of the database, I think we might need to rebuild searchcore_text and searchgroup_text. Even this might not be necessary because we are going to blow away and re-populate both of these tables anyway in the next step. Regardless, I am way more comfortable with running operations on these tables because they can be rebuilt from scratch if needed.

But enough jabbering. Log into your DB server and bring up a root MySQL shell. Rebuild those tables.

mysql> REPAIR TABLE searchcore_text QUICK;
mysql> REPAIR TABLE searchgroup_text QUICK;

Rebuilding the vB Search Index

This section should not cause total downtime, but searches may temporarily not return results, and overall performance may be slow.

vB has a section in the admin console where you can rebuild the search index. Don’t use that. There is another way to do this that doesn’t not rely on the web browser. Plus, if you know me by now, CLI is better!

If you still have the do_not_upload folder around, open that up. If not, log into your vB member’s area to get it. It is included with the vB forum download.

Upload the searchindex.php file to your vB server. Run it using something like this:

[user]$ php searchindex.php

This script can (and should) be ran as a normal user. I believe the script only needs read access to your forum directory. Here is the output from my run.

[user]$ php searchindex.php
Please enter the path to your vBulletin directory: /path/to/forums
Rebuild Search Index
You should not normally need to empty the search index, however if items are showing in search results after they have been permanently deleted then emptying the index and rebuilding the search index will fix this.  Search will not work correctly after emptying the index until the index is fully rebuilt.
Click Here to empty the search index.
Empty Index [0/1,Default=0]: 1

0) All
3) Forums
1) Posts
7) Groups
5) Group Messages
11) Visitor Messages
Content type to index [Default=0]: 0
Turn off DB indexes during rebuild [0/1,Default=0]:
Item id to start at [Default=0]:
Number of items to process per batch [Default=10000]:

Building Search Index Forums ...
IDs 0-37 ... Done (0.72 sec)
Building Search Index Forums: Done
Building Search Index Posts ...
IDs 0-10000 ... Done (76.38 sec)
IDs 10000-20000 ... Done (160.96 sec)
IDs 20000-30000 ... Done (163.44 sec)
IDs 4270000-4280000 ... Done (91.74 sec)
IDs 4280000-4288993 ... Done (84.23 sec)
Building Search Index Posts: Done
Building Search Index Groups ...
IDs 0-37 ... Done (0.28 sec)
Building Search Index Groups: Done
Building Search Index Group Messages ...
IDs 0-2573 ... Done (23.35 sec)
Building Search Index Group Messages: Done
Building Search Index Visitor Messages ...
IDs 0-10000 ... Done (56.13 sec)
IDs 10000-20000 ... Done (70.91 sec)
IDs 20000-25640 ... Done (61.13 sec)
Building Search Index Visitor Messages: Done
Building Search Index: 10 hours, 21 minutes and 55 seconds

I believe this will take significantly longer if you have ran this before – because the search index is now significantly larger. Once that is complete you are ready to test with a three-letter search.

This entry was posted in Guides and tagged on by .

About Andrew Wells

I have been developing on the LAMP stack since about 2006. I run Ubuntu XFCE on my desktop and have a history of managing Ubuntu and CentOS servers. I code web applications mostly in PHP but have experience with other languages as well. When I'm not working, I can be found working in my home lab or out snowboarding, hiking, camping, or biking depending on the season.

Leave a Reply

Your email address will not be published. Required fields are marked *