full text indexing only returns 2 rows

Question

I have a table for food and hotels like

CREATE TABLE `food_master` (
  `id` int(6) unsigned NOT NULL auto_increment,
  `caption` varchar(255) default NULL,
  `category` varchar(10) default NULL,
  `subcategory` varchar(10) default NULL,
  `hotel` varchar(10) default NULL,
  `description` text,
  `status` varchar(10) default NULL,
  `created_date` date default NULL,
  `modified_date` date default NULL,
  `chosen_mark` varchar(10) default 'no',
  PRIMARY KEY  (`id`),
  FULLTEXT KEY `description` (`description`,`caption`)
) ENGINE=MyISAM AUTO_INCREMENT=15 DEFAULT CHARSET=latin1

And I have data in it. I use full text indexing in this table. I use the query

SELECT * FROM food_master am 
WHERE MATCH(description, caption) AGAINST ('Chicken')

This query works fine when i have 2 'Chicken' in the field 'caption'. but when i put third one it doesnt return a row.

can you paste few table data because it even doesnt select even a single row; [`see here`](http://www.sqlfiddle.com/#!2/09d45/1) — xkeshav, Sep 14 '12 at 06:49
I made basic table: Please see here : http://www.sqlfiddle.com/#!2/f664e/2 — xkeshav, Sep 14 '12 at 06:57
sorry, i have used jfiddle to show the table content http://jsfiddle.net/arjuncc/t9pZU/ — arjuncc, Sep 14 '12 at 07:04
sir can u please use this query in yourown database "SELECT * FROM food_master am WHERE MATCH(description, caption) AGAINST ('Chicken ') ". I will upload the data after creating sql fiddle — arjuncc, Sep 14 '12 at 07:11

score 1 · Accepted Answer · answered Sep 14 '12 at 07:20

try with IN BOOLEAN MODE as

MySQL can perform boolean full-text searches using the IN BOOLEAN MODE modifier. With this modifier, certain characters have special meaning at the beginning or end of words in the search string.

SELECT * FROM food_master 
WHERE MATCH(description, caption) AGAINST ('Chicken' IN BOOLEAN MODE)

Demo

full text indexing only returns 2 rows

1 Answers1