Ken Ken - 6 months ago 30
MySQL Question

MySQL MATCH AGAINST not working

I'm actually working on a website where there are two tables, almost identical, where MATCH AGAINST works on one but not on the other. To find out why I've tried to reduce this to a simple, "Let's make a simple table work" - but it doesn't.

I'm doing testing with phpMyAdmin and this is MySQL 5.1.41.

The test I build used the following to define the table...

CREATE TABLE IF NOT EXISTS `test` (
`id` int(11) NOT NULL,
`title` text NOT NULL,
`body` text NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

ALTER TABLE `test` ADD FULLTEXT (`title`, `body`);


and when I do a SHOW INDEX FROM test I see that there is a FULLTEXT key comprising title and body.

I insert a couple of rows with

INSERT INTO `test` (`id`, `title`, `body`) VALUES
('1', 'Lorem Ipsum', 'Lorem ipsum dolor sit amet, consectetur ... lacus porta euismod.'),
('2', 'Lorem Ipsum (cont)', 'Nunc leo massa, vulputate ... euismod fringilla.');


(some body content removed for brevity)

and then when I run

SELECT * FROM `test` WHERE MATCH (`title`, `body`) AGAINST ('consectetur');


I get an empty result set - no rows found but if I run

SELECT * FROM `test` WHERE `body` LIKE '%consectetur%';


then a record is found.

Although I've a lot of experience with MySQL, this is the first time I've used MATCH so am I doing something daft? Why is this not working? Does the index need to be built (I've done a REPAIR on the table) or is it all supposed to happen automatically?

Just for information, the table which works is defined with

CREATE TABLE IF NOT EXISTS `web_pages1` (
`id` int(11) NOT NULL,
`title` varchar(255) DEFAULT NULL,
`slug` varchar(255) DEFAULT NULL,
`meta_keywords` text,
`meta_description` text,
`snippet` text,
`body` mediumtext,
`created_by` int(11) DEFAULT NULL,
`date_created` datetime DEFAULT NULL,
`date_published` date DEFAULT NULL,
`author` varchar(255) DEFAULT NULL,
`edited_by` int(11) DEFAULT NULL,
`date_edited` datetime DEFAULT NULL,
`status` tinyint(4) NOT NULL DEFAULT '1',
`parent_id` tinyint(11) DEFAULT NULL,
`menu_id` int(11) DEFAULT NULL,
`short_name` varchar(255) DEFAULT NULL,
`sort_order` int(11) DEFAULT NULL,
PRIMARY KEY (`id`),
FULLTEXT KEY `search` (`title`,`slug`,`meta_keywords`,`meta_description`,`snippet`,`body`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;


and the one which is causing me problems and sent me down this route is

CREATE TABLE IF NOT EXISTS `web_news1` (
`id` int(11) NOT NULL,
`title` varchar(255) NOT NULL,
`slug` varchar(255) NOT NULL,
`meta_keywords` text,
`meta_description` text,
`snippet` text NOT NULL,
`body` text NOT NULL,
`created_by` int(11) NOT NULL,
`date_created` datetime NOT NULL,
`date_published` date DEFAULT NULL,
`author` varchar(255) DEFAULT NULL,
`edited_by` int(11) DEFAULT NULL,
`date_edited` datetime DEFAULT NULL,
`status` tinyint(4) DEFAULT '0',
PRIMARY KEY (`id`),
FULLTEXT KEY `search` (`title`,`slug`,`meta_keywords`,`meta_description`,`snippet`,`body`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;


Although I've tried defining the second one exactly like the first and still it doesn't work.

Answer

you need to add IN BOOLEAN MODE at the end

SELECT * FROM `test` WHERE MATCH (title, body) AGAINST ('Ipsum' IN BOOLEAN MODE);

Please take a look here:

http://www.sqlfiddle.com/#!2/1b80b/2