多列全文索引是否更适合优化论坛搜索查询？

原搜索查询的WHERE子句（关键词为"6.18"）：

(
    INSTR(LOWER(author), "6.18")
 OR INSTR(LOWER(subject), "6.18")
 OR id IN (SELECT forum_text_id
             FROM `forum_text`
            WHERE MATCH(text) AGAINST("6.18")
              AND moderated IS NOT NULL)
 OR INSTR(LOWER(email), "6.18")
OR INSTR(LOWER(DATE(updated)), "6.18")
)

我已意识到可根据关键词优化：比如关键词含非数字/连字符内容时，无需检索updated日期字段（会预先将"July"这类词转换为"-07-"）。现在想了解：用跨多列的全文索引替代多个INSTR条件，是否是更优的实现方式？

拟创建的索引语句：

ALTER TABLE messages ADD FULLTEXT INDEX `messages-meta-information` (author, subject, email, updated);

优化后的查询语句：

(
    id IN (SELECT forum_text_id
             FROM `forum_text`
            WHERE MATCH(author, subject, email, updated) AGAINST("6.18")
 OR id IN (SELECT forum_text_id
             FROM `forum_text`
            WHERE MATCH(text) AGAINST("6.18")
              AND moderated IS NOT NULL)
)

请问这种多列全文索引方案是否比原查询更优？

整体来说，多列全文索引方案大概率比原查询更优，但要结合实际场景注意几个细节：

性能层面的核心优势 #

• 原查询中的INSTR(LOWER(col), "6.18")是全表扫描操作，每一行都要做字符串转换和匹配，数据量越大效率越低。而全文索引是预构建的倒排索引，匹配速度是数量级的提升，数据量过万后差距会非常明显。
• 多列全文索引把多个字段的匹配合并成一次索引查询，避免了原查询中多个OR条件导致的多次全表扫描叠加，执行效率会高很多。

需要注意的潜在问题 #

1. 短词匹配限制
   MySQL默认全文索引会忽略4个字符以下的短词（可通过ft_min_word_len参数调整），"6.18"是3个字符，默认情况下不会被索引，直接用AGAINST("6.18")会查不到结果。需要先调整参数（全局或表级）将最小词长改为3，再重建索引才能生效。
2. 日期字段的匹配失效风险
   把日期类型的updated加入全文索引后，日期转字符串的格式可能无法被分词器识别为"6.18"。比如"2024-06-18"会被默认分词器拆成"2024""06""18"，无法匹配"6.18"，但原查询的INSTR可以做到这点，必须测试验证是否符合需求。
3. 索引维护成本
   全文索引会占用额外存储空间，且当messages表有大量写入、更新操作时，索引会自动重建，消耗CPU和IO资源。如果论坛读写频繁，要评估这部分开销是否可接受。

折中优化建议 #

如果担心日期字段的匹配问题，可以把updated从多列全文索引中移除，仅对author、subject、email创建全文索引；同时保留原逻辑中“根据关键词判断是否添加日期字段匹配条件”的优化，这样既保留了全文索引的性能优势，又避免了日期字段的匹配失效问题。

内容的提问来源于stack exchange，提问作者Richard