Simple example that illustrates the differences in MySQL's full text MATCH in BOOLEAN MODE on a table using MyISAM storage engine vs one using InnoDB

full_text_myisam_vs_innodb_example.sql

DROP TABLE IF EXISTS word_match_test;

CREATE TABLE word_match_test (
	word varchar(63),
	FULLTEXT INDEX IX_word_match_test_word (word)
) ENGINE=MyISAM;

INSERT INTO word_match_test (word)
VALUES ('test butterfly mouse kitten test'),
	   ('test butterflymore mousemore test'),
	   ('test butterfly kitten test'),
	   ('test mouse kitten test'),
	   ('test butterflymore test'),
	   ('test mousemore test'),
	   ('test kitten test'),
	   ('butterfly mouse'),
	   ('mouse butterfly');

SELECT word, MATCH(word) AGAINST ('+butterfly +mouse' IN BOOLEAN MODE) AS has_both,
	         MATCH(word) AGAINST ('+butterfly* +mouse*' IN BOOLEAN MODE) AS has_both_partial,
             MATCH(word) AGAINST ('butterfly mouse' IN BOOLEAN MODE) AS has_either,
	         MATCH(word) AGAINST ('butterfly* mouse*' IN BOOLEAN MODE) AS has_either_partial
FROM word_match_test
ORDER BY has_both + has_both_partial + has_either + has_either_partial DESC;

/*
word                              has_both  has_both_partial  has_either  has_either_partial

test butterfly mouse kitten test  1         1                 2           2
butterfly mouse                   1         1                 2           2
mouse butterfly                   1         1                 2           2
test butterflymore mousemore test 0         1                 0           2
test butterfly kitten test        0         0                 1           1
test mouse kitten test            0         0                 1           1
test butterflymore test           0         0                 0           1
test mousemore test               0         0                 0           1
test kitten test                  0         0                 0           0

*/

DROP TABLE word_match_test;

CREATE TABLE word_match_test (
	word varchar(63),
	FULLTEXT INDEX IX_word_match_test_word (word)
) ENGINE=InnoDB;

INSERT INTO word_match_test (word)
VALUES ('test butterfly mouse kitten test'),
	   ('test butterflymore mousemore test'),
	   ('test butterfly kitten test'),
	   ('test mouse kitten test'),
	   ('test butterflymore test'),
	   ('test mousemore test'),
	   ('test kitten test'),
	   ('butterfly mouse'),
	   ('mouse butterfly');

SELECT word, MATCH(word) AGAINST ('+butterfly +mouse' IN BOOLEAN MODE) AS has_both,
	         MATCH(word) AGAINST ('+butterfly* +mouse*' IN BOOLEAN MODE) AS has_both_partial,
             MATCH(word) AGAINST ('butterfly mouse' IN BOOLEAN MODE) AS has_either,
	         MATCH(word) AGAINST ('butterfly* mouse*' IN BOOLEAN MODE) AS has_either_partial
FROM word_match_test
ORDER BY has_both + has_both_partial + has_either + has_either_partial DESC;


/*
word                              has_both  has_both_partial  has_either  has_either_partial

test butterfly mouse kitten test   0.248065  0.06201626360416  0.24806505  0.062016263604164124
butterfly mouse	                   0.248065  0.06201626360416  0.24806505  0.062016263604164124
mouse butterfly                    0.248065  0.06201626360416  0.24806505  0.062016263604164124
test butterfly kitten test         0         0                 0.12403252  0.031008131802082062
test mouse kitten test             0         0                 0.12403252  0.031008131802082062
test butterflymore mousemore test  0         0.06201626360416  0           0.062016263604164124
test butterflymore test            0         0                 0           0.031008131802082062
test mousemore test                0         0                 0           0.031008131802082062
test kitten test                   0         0                 0           0
*/

DROP TABLE word_match_test;