Why can't I MATCH() AGAINST() one of the columns in my FULLTEXT index?

Question

Here's the CREATE TABLE:

CREATE TABLE `EntityAddresses` (
  `EntityAddress_ID` int(11) NOT NULL AUTO_INCREMENT,
  `Entity_ID` int(11) NOT NULL,
  `CreateDate` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  `CreatedBy` int(11) NOT NULL,
  `UpdateDate` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  `UpdatedBy` int(11) NOT NULL,
  `StreetAddress` varchar(150) NOT NULL DEFAULT '',
  `StreetAddress2` varchar(150) NOT NULL DEFAULT '',
  `City` varchar(50) NOT NULL DEFAULT '',
  `State` varchar(50) NOT NULL DEFAULT '',
  `PostalCode` varchar(30) NOT NULL DEFAULT '',
  `Country` varchar(50) NOT NULL DEFAULT '',
  PRIMARY KEY (`EntityAddress_ID`),
  KEY `Entity_ID` (`Entity_ID`),
  KEY `CreatedBy` (`CreatedBy`),
  KEY `UpdatedBy` (`UpdatedBy`),
  FULLTEXT INDEX `EntityAddresses_ibft_1` (`StreetAddress`,`StreetAddress2`,`City`,`State`,`PostalCode`,`Country`), 
  CONSTRAINT `EntityAddresses_ibfk_1` FOREIGN KEY (`Entity_ID`) REFERENCES `Entities` (`Entity_ID`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `EntityAddresses_ibfk_2` FOREIGN KEY (`CreatedBy`) REFERENCES `Users` (`User_ID`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `EntityAddresses_ibfk_3` FOREIGN KEY (`UpdatedBy`) REFERENCES `Users` (`User_ID`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Here's an example record:

                       EntityAddress_ID: 5908
                              Entity_ID: 4514
                             CreateDate: 2015-05-25 13:59:43
                              CreatedBy: 2
                             UpdateDate: 2015-05-25 13:59:43
                              UpdatedBy: 2
                          StreetAddress: Testing 123
                         StreetAddress2: 
                                   City: Singapore
                                  State: 
                             PostalCode: 
                                Country: SG

Here's the query that should return the example record above:

SELECT 
    *
FROM
    `EntityAddresses`
WHERE
    MATCH (`StreetAddress` , `StreetAddress2` , `City` , `State` , `PostalCode` , `Country`) AGAINST ('+SG' IN BOOLEAN MODE);

When I try the same query with "Singapore" in place of "SG", it returns a result set including the example record above, but "SG" returns nothing. I have tried other countries and I'm getting the same result, 0 rows.

If 50% of your records contain the text you search for, then it will be ignored. Is this the case for you? — juergen d, May 26 '15 at 18:34
Also the default for minimum word length to be included in fulltext index is 3. — Marki555, May 26 '15 at 18:41
@Marki555 That would be the issue. Can you provide it as an answer with a reference to the documentation? I'll mark it as the answer. — Travis, May 26 '15 at 18:56
@juergend that is not the case for this, but I was not aware of that. thank you. — Travis, May 26 '15 at 19:10

score 2 · Accepted Answer · answered May 26 '15 at 19:28

MySQL fulltext engine has defined minimum word length to be included in the index. This is by default 3, so that's why it won't find your text SG.

You can change the MySQL server variable innodb_ft_min_token_size. The change will be visible for newly created and re-created indexes only.

MySQL manual for innodb_ft_min_token_size says this:

Minimum length of words that are stored in an InnoDB FULLTEXT index. Increasing this value reduces the size of the index, thus speeding up queries, by omitting common word that are unlikely to be significant in a search context, such as the English words “a” and “to”. For content using a CJK (Chinese, Japanese, Korean) character set, specify a value of 1.

Why can't I MATCH() AGAINST() one of the columns in my FULLTEXT index?

1 Answers1