title: MySQL MATCH AGAINST not working
body: <p>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.</p>
  
  <p>I'm doing testing with phpMyAdmin and this is MySQL 5.1.41.</p>
  
  <p>The test I build used the following to define the table...</p>
  
  <pre><code>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`);
  </code></pre>
  
  <p>and when I do a SHOW INDEX FROM test I see that there is a FULLTEXT key comprising title and body.</p>
  
  <p>I insert a couple of rows with</p>
  
  <pre><code>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.');
  </code></pre>
  
  <p>(some body content removed for brevity)</p>
  
  <p>and then when I run</p>
  
  <pre><code>SELECT * FROM `test` WHERE MATCH (`title`, `body`) AGAINST ('consectetur');
  </code></pre>
  
  <p>I get an empty result set - no rows found but if I run </p>
  
  <pre><code>SELECT * FROM `test` WHERE `body` LIKE '%consectetur%';
  </code></pre>
  
  <p>then a record is found.</p>
  
  <p>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?</p>
  
  <p>Just for information, the table which works is defined with</p>
  
  <pre><code>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;
  </code></pre>
  
  <p>and the one which is causing me problems and sent me down this route is</p>
  
  <pre><code>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;
  </code></pre>
  
  <p>Although I've tried defining the second one exactly like the first and still it doesn't work.</p>