LIKE '%chapter%' cannot use a B-tree and knows nothing about words. A FULLTEXT index is an inverted index: the parser splits text into tokens, and InnoDB records the rows and positions of each. MATCH (cols) AGAINST ('words') searches in natural language mode by default and scores rows higher for words rare in the collection; in WHERE without ORDER BY, rows come back best first, so AGAINST ('testing chapter') returned review 6 (score 1.01) before review 2 (0.30), which lacks "testing". The MATCH column list must equal an index's list exactly.
ALTER TABLE reviews ADD FULLTEXT INDEX ft_body (body);
ALTER TABLE products ADD FULLTEXT INDEX ft_title (title);
SELECT SUM(MATCH (body) AGAINST ('index') > 0) AS `index`,
SUM(MATCH (body) AGAINST ('indexes') > 0) AS indexes,
SUM(MATCH (body) AGAINST ('B-trees') > 0) AS `b-trees`,
SUM(MATCH (body) AGAINST ('the') > 0) AS the FROM reviews;+-------+---------+---------+------+ | index | indexes | b-trees | the | +-------+---------+---------+------+ | 0 | 1 | 1 | 0 | +-------+---------+---------+------+
Each zero is a parser rule: no stemming (index misses "indexes"), hyphens split words (B-trees matched via "trees"), and "the" is one of 36 default stopwords. Tokens shorter than innodb_ft_min_token_size (3) are never indexed, so sql matched a title and UX none; MyISAM's ft_min_word_len is 4. The first FULLTEXT index adds a hidden FTS_DOC_ID column and rebuilds the table (warning 124); these two indexes created 22 auxiliary fts_ tables.