0

In Mysql full text search, i've search an phrase "it department" in the indexed field. But it returns the results containing the word department instead of "it department". please guide me to resolve this issue.

Following is the syntax i used,

select * from employees where match(contents) against("it department")

Thanks in advance

Your Common Sense
  • 156,878
  • 40
  • 214
  • 345
kumaranc
  • 105
  • 1
  • 11
  • http://stackoverflow.com/questions/10931701/mysql-where-match-against – JustOnUnderMillions Feb 24 '17 at 12:09
  • And read here https://dev.mysql.com/doc/refman/5.5/en/fulltext-search.html `Posted by Patrick O'Lone on December 9, 2002` it seems that in your case both words a matched seperate. match `it` and match `department`, thing you have to fix this. – JustOnUnderMillions Feb 24 '17 at 12:11
  • Simple do `select * from employees where contents like '%it department%'`, if dont get it fixed – JustOnUnderMillions Feb 24 '17 at 12:13
  • Thanks for the reply, Yes i've achieve it when i use regex but i have large set of database. Even full text search takes two hours to execute. – kumaranc Feb 24 '17 at 12:29
  • But i'm using phrase query, I even not using any operator to decide the priority and boolean too. This is very strange thing am seeing. – kumaranc Feb 24 '17 at 12:30
  • Possible duplicate of [mysql WHERE MATCH AGAINST](http://stackoverflow.com/questions/10931701/mysql-where-match-against) – Muhammad Saqlain Feb 24 '17 at 13:42

2 Answers2

0

MySQL FULLTEXT uses a stoplist of common English words. Your favorite word it is on the stoplist. The purpose of a stoplist is to keep the index size reasonable.

If you're using MySQL version 5.7+ and InnoDB, you can rig up your own stoplist.

This explains how.

https://dev.mysql.com/doc/refman/5.7/en/fulltext-stopwords.html

O. Jones
  • 103,626
  • 17
  • 118
  • 172
0

you should be use 'like' or 'regexp' instead

select * from employee where contents regexp'^it department$';

or

select * from employee where contents like 'it department';
denny
  • 2,084
  • 2
  • 15
  • 19