Member Avatar for Alex Matulich
Alex Matulich 0 Newbie Poster

I need to set ft_min_word_length=3, so that my users can perform boolean fulltext searches for words of three characters instead of four. However, I share the MySQL server with other customers of my ISP.

My ISP's administrator is willing to change this system variable, provided I can show him an authoritative source that says doing so won't break anything if other customers don't rebuild their FULLTEXT indexes.

The description on http://dev.mysql.com/doc/refman/5.1/en/fulltext-fine-tuning.html contains no warnings; the text only implies that setting ft_min_word_len=3 will have no effect until you rebuild your FULLTEXT indexes (which should continue to work as they did before).

The following quote from my admin's latest email explains his problem:

Can you find any authoritative source that actually says that the only consequence is that text searches won't find any 3-character words because the fulltext index won't contain them until it's rebuilt? The MYSQL manual doesn't clearly say this, and it does say that "you must rebuild your fulltext index."

There are quite a few other MySQL databases on the server, many of them automatically created when installatron is used, and I have no idea if any of them use fulltext searches. I can't afford the risk of any of them breaking. So all I need is some authoritative source that says that existing databases will not be affected.

Anybody know of an authoritative source that explains the consequences?

  • 1 Contributor
  • 0 Replies
  • 114 Views

Dani AI

Generated

Short answer for : changing ft_min_word_len does not silently rewrite or break existing FULLTEXT indexes for other databases — the setting is used when an index is created, and existing indexes remain as they were until explicitly rebuilt. That means other customers’ full‑text searches continue to behave the same unless and until their indexes are rebuilt. See the MySQL reference on full‑text tuning for the official explanation: MySQL manual — Full‑text fine tuning.

Practical checklist you can give the admin to prove safety and to minimise risk:

  • Find every FULLTEXT index on the server (run as root or an account with access to information_schema):

    SELECT TABLE_SCHEMA, TABLE_NAME, INDEX_NAME,
     GROUP_CONCAT(COLUMN_NAME ORDER BY SEQ_IN_INDEX) AS columns
FROM information_schema.STATISTICS
WHERE INDEX_TYPE = 'FULLTEXT'
GROUP BY TABLE_SCHEMA, TABLE_NAME, INDEX_NAME;

    This shows whether other customers actually have FULLTEXT indexes.

  • If you own any FULLTEXT indexes, rebuild only those after the change. A non-invasive way to rebuild is:

    OPTIMIZE TABLE yourdb.yourtable;

    (OPTIMIZE will rebuild the table/index for both MyISAM and InnoDB in modern MySQL versions). Alternatively drop and re-create the FULLTEXT index for a specific table.

  • Coordinate maintenance windows. Rebuilding can be I/O‑heavy and may lock tables.

Caveats/notes: changing ft_min_word_len by itself only changes how future index builds treat short words. If a third party later chooses to rebuild their own index, their search results can change — but that is their action, not an automatic side effect of your configuration change. The MySQL manual link above is authoritative and should be acceptable to the admin.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.