为WordPress文件管理插件优化搜索：CONTAINS无结果排查

Great question—let’s break this down step by step, since you’re spot-on about the LIKE issue and there are a few common gotchas with full-text search that might be tripping up your query.

First: Your understanding of LIKE %abcabc% vs FULLTEXT is correct! #

When you use a leading wildcard (%) in a LIKE clause (like %abcabc%), MySQL (the most common database for WordPress) cannot use any index—including FULLTEXT indexes—to optimize the query. It has to run a full table scan, which gets painfully slow with 10k+ rows. FULLTEXT indexes are designed for natural language or boolean full-text searches, not arbitrary substring matches with wildcards on both ends. So swapping to a proper full-text method is absolutely the right move for performance.

Why isn’t CONTAINS(column, 'term') returning results? #

First, a quick clarification: If you’re using MySQL (WordPress’s default database), CONTAINS isn’t the correct syntax—MySQL uses MATCH(column) AGAINST('term') instead. CONTAINS is for SQL Server, so if you’re on MySQL, that’s probably the first issue! Assuming you might have mixed up syntax, here’s a checklist to troubleshoot:

• Verify your full-text index exists
  Make sure the column you’re searching has a FULLTEXT index. Run this query to check:

  SHOW INDEX FROM your_table_name WHERE Key_name LIKE '%fulltext%';
  

  If no index exists, create it with:

  ALTER TABLE your_table_name ADD FULLTEXT INDEX idx_fulltext_your_column (your_column_name);
  

• Check minimum word length restrictions
  MySQL’s full-text indexes ignore words shorter than a default threshold:

  • InnoDB: Default is 3 characters (innodb_ft_min_token_size)
  • MyISAM: Default is 4 characters (ft_min_word_len)
    If your search term is shorter than this, it won’t be matched. You can check these values with:

  SHOW VARIABLES LIKE 'innodb_ft_min_token_size';
  SHOW VARIABLES LIKE 'ft_min_word_len';
  

  To change them, update your MySQL config (my.cnf/my.ini), restart MySQL, then rebuild the FULLTEXT index.

• Watch out for stopwords
  MySQL has a built-in list of "stopwords" (common words like "the", "and", "is") that are ignored in full-text searches. If your search term is a stopword, you’ll get no results. You can check the stopword list or adjust the ft_stopword_file variable to customize it.

• Avoid natural language mode frequency filters
  In MySQL’s default natural language mode, if a term appears in more than 50% of rows, it’s treated as a stopword and ignored. To bypass this, use boolean mode instead:

  MATCH(your_column_name) AGAINST('+your_search_term' IN BOOLEAN MODE)
  

  The + forces an exact match for the term.

• Confirm character compatibility
  Ensure your table uses a character set that supports full-text indexing, like utf8mb4 (WordPress’s recommended charset). Older charsets like latin1 might have issues, especially with special characters.

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