3

I'm hoping someone can help with a query I have regarding MYSQL stopwords and match

If I were to run the below mysql query:

CREATE TABLE `tbladvertstest` (`id` int(11) unsigned NOT NULL AUTO_INCREMENT,`SearchCompany` varchar(250) DEFAULT NULL,PRIMARY KEY (`id`),FULLTEXT KEY `SearchCompany` (`SearchCompany`)) ENGINE=MyISAM DEFAULT CHARSET=utf8;


INSERT INTO `tbladvertstest` (`id`, `SearchCompany`) VALUES (NULL, 'All Bar One');


SELECT * FROM `tbladvertstest` WHERE MATCH (`SearchCompany`) AGAINST ('"All Bar One"' IN BOOLEAN MODE)

I thought the query would return all results where 'SearchCompany' is "All Bar One" but there are zero rows returned. I'm assuming Mysql is checking against each word individually rather than looking at the full string and the stopwords and minium word lengths are the reason it not returning any results? Would I be right? If so is it possible to get MySQL to see it as a string?

user3922793
  • 33
  • 3

2 Answers2

2

By default, MySQL FULLTEXT indexes will not index words shorter than 4 characters long (for MyISAM tables). If you want to index 3 letter words, you need to set the ft_min_word_len system variable (or innodb_ft_min_token_size for InnoDB) to a value of 3 or lower, then restart mysqld and rebuild your table.

For example, add this to the [mysqld] section of your my.cnf file:

ft_min_word_len=3

Then restart mysqld.

Then run this command to rebuild the table:

alter table `tbladvertstest` force;

Now your query should work:

mysql > SELECT * 
    -> FROM `tbladvertstest` 
    -> WHERE MATCH (`SearchCompany`) AGAINST ('+"All Bar One"' IN BOOLEAN MODE) ;
+----+---------------+
| id | SearchCompany |
+----+---------------+
|  1 | All Bar One   |
+----+---------------+
1 row in set (0.00 sec)
Ike Walker
  • 64,401
  • 14
  • 110
  • 109
1

You need to specify the operators... If you want to require the whole phrase, it needs to be in quotes:

SELECT * FROM table WHERE MATCH(SearchCompany) AGAINST ('+"All Bar One"' IN BOOLEAN MODE);
D. Pinheiro
  • 646
  • 4
  • 18
  • Hello, based on my amended question I've tried the below: SELECT * FROM tbladvertstest WHERE MATCH(`SearchCompany`) AGAINST ('+"All Bar One"' IN BOOLEAN MODE); but no luck? – user3922793 Oct 17 '17 at 11:26