Just checking I'm understanding this correctly:
CREATE TABLE customer ( id INT NOT NULL AUTO_INCREMENT, name TINYTEXT NOT NULL, PRIMARY KEY (id), FULLTEXT (name) ) ENGINE = InnoDB; INSERT INTO customer VALUES (1, "ABC.DEF"); INSERT INTO customer VALUES (2, "ABC_DEF"); INSERT INTO customer VALUES (3, "ABC'DEF"); INSERT INTO customer VALUES (4, "ABC.DE"); INSERT INTO customer VALUES (5, "ABC.DFF");
Where I've got
innodb_ft_min_token_size set to 3 (default is 4).
SELECT c.*, MATCH (name) AGAINST ("+ABC +DEF" IN BOOLEAN MODE) AS m FROM customer AS c
Customers 1 and 3 match, because the
' are seen as word separators (annoying for O'Brien).
For customer 2, because the underscore gets the whole name treated as a single word, the "DEF" word cannot be found.
If I change the MATCH to
1, 2, or 3 do not match because this is using a full word match (
"+DE" does not match
4 does not match because...
innodb_ft_min_token_size is set to 3?
As in, the 2 letter "DE" word is not in the FULLTEXT INDEX?
If I change the MATCH to use asterisks (e.g.
"+ABC* +DE*"), that will use prefix matching.
But will only add customers 1 and 3 to the selection.
Because the 2 letter "DE" word for customer 4 is not in the FULLTEXT INDEX?
If I change the MATCH to use
"+ABC.DE*", it matches all of them.
Note how they all get the same rank (even customer 5), and this is no different to
"+ABC*", where MATCH seems to be seeing the "DE*" as a separate word, and not matching it against anything.
"+ABC DE*" is explicitly keeping it as a separate word, and the scores are handled appropriately.
While the individual points make sense, I'm not sure this creates a good system.
For a bit more consistency, I'm wondering if the database should ignore short words (tokens) in the MATCH query, in the same way it does when building the FULLTEXT INDEX.
Only because I don't think
"+DE" will ever do anything useful when the min token size is 3; and it's not exactly easy for the developer to identify what the individual words in the FULLTEXT INDEX will be (i.e. to remove them).