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.

Answer 1

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.