2

First query returns nothing, second returns 0 and third returns zero against all records. I removed IN NATURAL LANGUAGE MODE but no change. Any reason why?

I've checked many examples e.g.:

DB)

SELECT
*
FROM person
WHERE
MATCH(`name`, `middlename`, `surname`) AGAINST ('John' IN NATURAL LANGUAGE MODE);


SELECT
COUNT(*)
FROM person
WHERE
MATCH(`name`, `middlename`, `surname`) AGAINST ('John' IN NATURAL LANGUAGE MODE);


SELECT
id,
MATCH(`name`, `middlename`, `surname`) AGAINST ('John' IN NATURAL LANGUAGE MODE) AS score
FROM person
ORDER BY score DESC;


DROP TABLE IF EXISTS `person`;
CREATE TABLE `person` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(45) COLLATE utf8_unicode_ci DEFAULT NULL,
  `middlename` varchar(45) COLLATE utf8_unicode_ci DEFAULT NULL,
  `surname` varchar(45) COLLATE utf8_unicode_ci DEFAULT NULL,
  `code` varchar(45) COLLATE utf8_unicode_ci DEFAULT NULL,
  PRIMARY KEY (`id`),
  FULLTEXT(`name`,`middlename`,`surname`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;


INSERT INTO `person` (`name`, `middlename`, `surname`, `code`) VALUES
('John', 'Joseph', 'Travolta', 'JJT'),
('John', '', 'Lenon', 'JL'),
('John', '', 'Wayne', 'JW'),
('John', 'Paul', 'John', 'JPJ'),
('Robert', '', 'DeNiro', 'RD'),
('Elton', '', 'John', 'EJ'),
('Abi', 'John John', '', 'AJ'),
('Johny', '', '', 'J'),
('John', 'John', 'John', 'JJJ');
Community
  • 1
  • 1
BentCoder
  • 10,692
  • 17
  • 82
  • 143

1 Answers1

4

This result is documented. You're using MyISAM table and your keyword "John" is present in at least 50% of the rows and so is a stopword, see manual, Natural Language Full-Text Searches

MyISAM Limitation For very small tables, word distribution does not adequately reflect their semantic value, and this model may sometimes produce bizarre results for search indexes on MyISAM tables. For example, although the word “MySQL” is present in every row of the articles table shown earlier, a search for the word in a MyISAM search index produces no results:

mysql> SELECT * FROM articles
WHERE MATCH (title,body)
AGAINST ('MySQL' IN NATURAL LANGUAGE MODE); Empty set (0.00 sec)

The search result is empty because the word “MySQL” is present in at
least 50% of the rows, and so is effectively treated as a stopword. This filtering technique is more suitable for large data sets, where you might not want the result set to return every second row from a 1GB table, than for small data sets where it might cause poor results for popular terms.

You could use the InnoDB Storage Engine to get around this feature:

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.

Another possibility is to use Boolean Full-Text Searches:

They do not use the 50% threshold that applies to MyISAM search indexes.

VMai
  • 9,648
  • 9
  • 21
  • 34