Searching smaller words

[dgatwood]

The four character lower limit is a real PITA, as most microphones have two-character or three-character names.

WARNING: the index could grow significantly.

WARNING: the tables will be locked during this process, so this will require downtime; I have no idea how long---that depends on how fast the server is and how big the tables are. It might be a good idea to dump the tables first and try it offline on a different machine.

With those warnings in mind, to change the indexing, first make sure the BBS is using MySQL fulltext indexing and not the built-in vBulletin indexing. If it is using the vBulletin searching, that could explain why searches bring the board to its knees....

Once you have switched to use the MySQL indexing (read the vBulletin docs for info), the next step is to tune MySQL appropriately. This is a multi-stage process.

First, in your MySQL configuration file (e.g. my.cnf), add:

[mysqld]
ft_min_word_len=2
​

Next, restart MySQL.

Next, rebuild the fulltext index for any tables that have them. To do this, for each table, type:

SHOW INDEX FROM table_name;
​

See http://dev.mysql.com/doc/refman/5.0/en/show-index.html for info.

This should show the indices for that table. For any index of type FULLTEXT, drop the index with:

ALTER TABLE table_name DROP INDEX index_name;
​

See http://dev.mysql.com/doc/refman/5.0/en/alter-table.html for info.

and recreate it with:

CREATE FULLTEXT INDEX index_name on table_name;
​

See http://dev.mysql.com/doc/refman/5.0/en/create-index.html for info.