Home > General > Change the full text index minimum word length with MySQL

Change the full text index minimum word length with MySQL

The MySQL full text index by default only indexes words which are 4 characters or longer, which means on a blog like mine if you search for “PHP” nothing will be returned. This post shows how to change minimum word length in MySQL so words or 3 characters (or even 2 if you want) can be indexed as well.
Edit the MySQL configuration file my.cnf which is usually located on a *nix box at /etc/my.cnf or /etc/mysql/my.cnf and add the following line under the [mysqld] section to change the default to 3:

ft_min_word_len = 3

If the ft_min_word_len value is already in the file then change the number to the minimum length required.

After making this change the MySQL server must be restarted for it to take effect. It is not possible to change the size with a set query (e.g. “SET ft_min_word_len = 3” which will result in the error “#1193 – Unknown system variable ‘ft_min_word_len'”).

Now that the minimum word length has changed, and new or updated records will use the new minimum word length, but existing records will not be affected. To rebuild the full text index on a column for an example table called my_table, run this query:

REPAIR TABLE my_table QUICK;

Advertisements
Categories: General Tags:
  1. Rohit
    August 15, 2009 at 2:27 pm

    Does the word ACC ring a bell?
    + I think the repair part was added thx to my misadventure? 😀

  2. September 1, 2009 at 2:46 am

    This is good to know. I have not used indexes that much and had been looking for this info. Thanks for sharing

  3. February 7, 2010 at 9:15 am

    it might not be settable with a SET, but can it be set when defining the FULLTEXT column itself?

    a la “create table (id int primary key, data text, fulltext(data, 2))”, with a minimum length of ‘2’ at column definition time.

    It’s quite annoying that the mysql people forgot that substring length is dependent on the language (for English words, 4 makes sense, for Chinese words, it means your index will be empty because there are virtually no 4 character words).

    • preetul
      March 4, 2010 at 11:41 am

      Hey,
      Sorry for delayed reply.
      Really good solution.
      Although i haven’t used yet, I found it very handy!

  1. August 16, 2009 at 6:19 pm

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: