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.:
- MySQL Full text search
- MySQL match() against() - order by relevance and column?
- how to get the result of mysql match against in form of percentage?
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');