CODEWITHSUNDEEP
mysqlmatchbrackets
Danbuntu
Danbuntu

Reputation: 165

mysql match against not matching text in brackets

I'm trying to use match against to return search results. I have a problem though where it's not returning results where the matching text is in brackets. Just getting rid of the brackets isn't an option i'm afraid.

So running the sql fiddle below I would expect it to return two results, not one

CREATE TABLE `courses` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `course_code` varchar(100) NOT NULL,
  `course_name` varchar(100) DEFAULT NULL,
  `startdate` varchar(100) DEFAULT NULL,
  `starttimestamp` varchar(45) DEFAULT NULL,
  `prospectus_title` varchar(500) DEFAULT NULL,
  PRIMARY KEY (`id`),
  FULLTEXT KEY `info` (`course_name`,`prospectus_title`,`course_code`)
) ENGINE=MyISAM AUTO_INCREMENT=981074 DEFAULT CHARSET=utf8;


INSERT INTO `courses` (`id`, `course_code`, `course_name`, `startdate`, `starttimestamp`, `prospectus_title`) VALUES 
('1', '1234', 'vrqtest', 'time','time', 'vrqtest'), 
('2', '5678', '(vrq)test', 'time','time','(vrq)test');

SELECT * FROM courses force index(info) 
WHERE starttimestamp IS NOT NULL AND ( 
MATCH ( course_name ) against ('vrq*' in boolean mode ))

SQL Fiddle

That returns only the first record but should also the second.

Any ideas?

Upvotes: 3

Views: 847

Answers (1)

erKURITA
erKURITA

Reputation: 407

Here's why:

https://dev.mysql.com/doc/refman/5.5/en/server-system-variables.html#sysvar_ft_min_word_len

ft_min_word_len

The minimum length of the word to be included in a FULLTEXT index. Defaults to 4

(vrq)test is too short to match. Add an extra character (e.g. (vrqa)test ) and it's matched.

Upvotes: 1

Related Questions