Thread: Full text search returns an empty string, can't understand

Hello,
I have an sql search with data in a table which should be returned. However the I am getting an empty set back.
I enclose below the SQL I am using and the table:

Code:

SELECT poisonId, poison, latin_name, short_desc, status FROM tblpoisons WHERE MATCH (poison,latin_name,short_desc,full_desc) AGAINST ('%poison%')

Here is the table:

Code:

CREATE TABLE `tblpoisons` (  `poisonId` int(11) NOT NULL auto_increment,  `catId` int(11) default NULL,  `poison` varchar(150) default NULL,  `latin_name` varchar(150) default NULL,  `short_desc` varchar(250) default NULL,  `Full_desc` mediumtext,  `status` tinyint(1) default NULL,  PRIMARY KEY  (`poisonId`),  FULLTEXT KEY `searchindex` (`poison`,`latin_name`,`short_desc`,`Full_desc`)) ENGINE=MyISAM AUTO_INCREMENT=4 DEFAULT CHARSET=latin1;
 

Many thanks

Have you inserted any data in the table?

The SQL you included creates an empty table.

Yes there is data added, it should return some data as the word searched is included in the fields

Maybe try a LIKE statement??

You need to add more rows to the table or change the query.

MySQl has a 50% threshold on the Match Against construct meaning that if your query matches more than 50% of the rows in the table it does not consider the match relevant so it does not return the rows.

So you add more rows without the text you are querying for and you'll get results ...

Or you can use the "In boolean mode" by changing your query to

Code:

SELECT poisonId, poison, latin_name, short_desc, status 
FROM tblpoisons
WHERE MATCH (poison,latin_name,short_desc,full_desc) 
AGAINST ('%poison%' in boolean mode)