Can't search for three-letter words?

[xtf]

Hi,

It used to be possible to search for three-letter words, like XBT. Why is this no longer possible?

Could you enable it again please?

See also http://www.vbulletin.org/forum/showthread.php?p=1142827

[peterska2]

This is because the sites now use full text searching and is set to a minimum of 4 characters.

[xtf]

This is because the sites now use full text searching and is set to a minimum of 4 characters.

Could you change the configuration to 3?

[Floris]

No sorry.

[Lats]

The wildcard still works, so enter xbt* to get results.

[xtf]

No sorry.

Why not?

[Wayne Luke]

There really aren't that many 3-letter words to search for and get meaningful results. The cost of reconfiguring all our servers, restarting them and rebuilding all the indexes plus the overhead with continual use aren't justified.

[Scott MacVicar]

We have got plans to do it at some point, the old servers had 3 letter full text indexes.

The reason we can't is that it requires a serious amount of downtime to rebuild 21 fulltext indexes on a large amount of tables. We can't really afford to be down for that long.

[xtf]

The wildcard still works, so enter xbt* to get results.

That doesn't work. It searches for words of at least four letters that start with xbt.

[jasonlitka]

We have got plans to do it at some point, the old servers had 3 letter full text indexes.

The reason we can't is that it requires a serious amount of downtime to rebuild 21 fulltext indexes on a large amount of tables. We can't really afford to be down for that long.

I recently extended my search from 3-12 to 3-15 and it doesn't need to be done all at once. Just reconfigure the MySQL servers to have a min length of 3 instead of 4 and restart them.

At that point you can just schedule a few indexes per day and rebuild them. I didn't have more than a few minutes of downtime per day on any given site and I have 12G of DB data (well, 24GB if you count the slave).

[Scott MacVicar]

We tried already without restarting and got several corrupt index files that we had to rebuild. This was on the slave servers as well as the master.

The manual also states they need to get rebuilt.

• The minimum and maximum lengths of words to be indexed are defined by the ft_min_word_len and ft_max_word_len system variables. (See Section 5.2.3, “System Variables”.) The default minimum value is four characters; the default maximum is version dependent. If you change either value, you must rebuild your FULLTEXT indexes. For example, if you want three-character words to be searchable, you can set the ft_min_word_len variable by putting the following lines in an option file:
  

  [mysqld]
  ft_min_word_len=3

  Then you must restart the server and rebuild your FULLTEXT indexes. Note particularly the remarks regarding myisamchk in the instructions following this list.

[jasonlitka]

We tried already without restarting and got several corrupt index files that we had to rebuild. This was on the slave servers as well as the master.

The manual also states they need to get rebuilt.

Well, you do need to restart mysqld after you change those values. What you don't need to do is actually run "REPAIR TABLE ... QUICK" immediately after the restart. Your DB will continue to function as it did before, with a minimum of 4 characters, until you do run the REPAIR statements.

What I was saying is that you can space those REPAIR statements out over a week or so so that each outage is short.

You can control the downtime even more closely by not allowing the rebuild to cascade automatically to the slave server(s). By running the REPAIR on your slave server(s) first and then adding "LOCAL" after the word "REPAIR" when running on the master, you can halve the downtime for each rebuild session.

[xtf]

We tried already without restarting and got several corrupt index files that we had to rebuild. This was on the slave servers as well as the master.

The manual also states they need to get rebuilt.

How did that happen?
I thought MySQL didn't read the conf files while it's running?

[jasonlitka]

How did that happen?
I thought MySQL didn't read the conf files while it's running?

Ooh... I wonder if they tried running 'myisamchk' while mysqld was running. That could be how the corruption happened. You really should have mysqld stopped when you do that...

[Scott MacVicar]

I'll be doing it in when we have our upgrade to Apache 2.2.4 scheduled, but we got plenty of errors about the index file being corrupted and every single one of them was a table containing a full text index.

I'll do the changes when we have time, not before. Searching for 3 letter words isn't something that justifies downtime.