MyISAMとInnoDBでFULLTEXT SEARCHは完全に同じだと思い込んでたのでハマった。
MyISAMのNATURAL LANGUAGEモードの検索では半分以上の行に含まれるワードは検索対象にならない。InnoDBではその仕様はなくなってる。NATURAL LANGUAGEモードを使うケースほとんどないから困らないけど、テストケースが通らなくてハマった。
mysql> CREATE TABLE myisam (a TEXT, FULLTEXT(a)) CHARSET latin1 ENGINE=MyISAM;
Query OK, 0 rows affected (0.00 sec)
mysql> CREATE TABLE innodb (a TEXT, FULLTEXT(a)) CHARSET latin1 ENGINE=InnoDB;
Query OK, 0 rows affected (0.24 sec)
mysql> INSERT INTO myisam VALUES('ab cd DE'), ('aa bb DE'), ('cc dd DE');
Query OK, 3 rows affected (0.00 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql> INSERT INTO innodb VALUES('ab cd DE'), ('aa bb DE'), ('cc dd DE');
Query OK, 3 rows affected (0.02 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql> SELECT * FROM myisam WHERE MATCH(a) AGAINST('DE' IN NATURAL LANGUAGE MODE);
Empty set (0.00 sec)
mysql> SELECT * FROM innodb WHERE MATCH(a) AGAINST('DE' IN NATURAL LANGUAGE MODE);
+----------+
| a |
+----------+
| ab cd DE |
| aa bb DE |
| cc dd DE |
+----------+
3 rows in set (0.00 sec)
「50%ルールはMyISAMだけ」とマニュアルにもちゃんと書いてあった。
The 50% threshold can surprise you when you first try full-text searching to see how it works, and makes InnoDB tables more suited to experimentation with full-text searches. If you create a MyISAM table and insert only one or two rows of text into it, every word in the text occurs in at least 50% of the rows. As a result, no search returns any results until the table contains more rows. Users who need to bypass the 50% limitation can build search indexes on InnoDB tables, or use the boolean search mode explained in Section 12.9.2, “Boolean Full-Text Searches”.
