Boolean Mode and Relevance

IN BOOLEAN MODE adds operators: + requires a word, - excludes it, * matches a prefix, "..." a phrase, > and < change a word's weight, ~ makes it count against a row, "a b" @8 requires the words within eight of each other, and parentheses group. Results are unsorted unless you ORDER BY the MATCH expression.

SELECT GROUP_CONCAT(IF(MATCH (body) AGAINST ('+chapter -testing' IN BOOLEAN MODE), id, NULL))
         AS `+chapter -testing`,
       GROUP_CONCAT(IF(MATCH (body) AGAINST ('index*' IN BOOLEAN MODE), id, NULL)) AS `index*`,
       GROUP_CONCAT(IF(MATCH (body) AGAINST ('"window functions"' IN BOOLEAN MODE), id, NULL))
         AS `"window functions"`,
       GROUP_CONCAT(IF(MATCH (body) AGAINST ('+the +chapter' IN BOOLEAN MODE), id, NULL))
         AS `+the +chapter`
  FROM reviews\G

*************************** 1. row ***************************
 +chapter -testing: 2
            index*: 3
"window functions": 2
     +the +chapter: NULL

index* fixes the stemming miss, but a required stopword (+the) can never match. >testing lifted review 6 from 1.01 to 2.01 and ~testing cut it to 0.0102. InnoDB ranks a word as TF x IDF x IDF, with IDF = log10(rows / matching rows), so tiny tables distort scores: on three rows that all say "MySQL 524 ", all three came back scoring 0.0000000019. MyISAM, by contrast, drops a word found in half the rows.

For your own stopwords, fill a one-column value VARCHAR(30) table, SET SESSION innodb_ft_user_stopword_table = 'shop/shop_stopwords', and recreate the index with separate DROP INDEX and CREATE FULLTEXT INDEX statements (a combined ALTER TABLE kept the old list). Listing chapter made it match 0 reviews and the 4: a custom list replaces the default. For Chinese titles such as 数据库索引入门, the default parser sees one token and AGAINST ('数据库') found nothing; WITH PARSER ngram indexes every 2-character run (ngram_token_size) and found both titles containing it.