title: MySQL Full Text Search Mystery
body: <p>We have a simple search on our site that uses MySQL fulltext search and for some reason it doesn't seem to be returning the correct results. I don't know if it's some kind of issue with Amazon RDS (where our database server resides) or with the query we are requesting. </p>
  
  <p>Here is the structure of the database table:</p>
  
  <pre><code>CREATE TABLE `items` (
    `object_id` int(9) unsigned NOT NULL DEFAULT '0',
    `slug` varchar(100) DEFAULT NULL,
    `name` varchar(100) DEFAULT NULL,
    PRIMARY KEY (`object_id`),
    FULLTEXT KEY `name` (`name`)
  ) ENGINE=MyISAM DEFAULT CHARSET=latin1;
  </code></pre>
  
  <p>And here is a simple fulltext search query on this table and the returned results:</p>
  
  <pre><code>select object_id ,slug,name from items where MATCH (name) AGAINST ('+ski*' IN BOOLEAN MODE) order by name;
  
  +-----------+-----------------------------------+------------------+
  | object_id | slug                              | name             |
  +-----------+-----------------------------------+------------------+
  |  10146041 | us/new-hampshire/dartmouth-skiway | Dartmouth Skiway |
  +-----------+-----------------------------------+------------------+
  </code></pre>
  
  <p>If I instead use LIKE I get a different set of results:</p>
  
  <pre><code>select object_id,slug,name from items where name LIKE "%ski%" order by name;
  
  +-----------+------------------------------------------+----------------------------------+
  | object_id | slug                                     | name                             |
  +-----------+------------------------------------------+----------------------------------+
  |  10146546 | us/new-york/brantling-ski                | Brantling Ski                    |
  |  10146548 | us/new-york/buffalo-ski-club             | Buffalo Ski Club                 |
  |  10146041 | us/new-hampshire/dartmouth-skiway        | Dartmouth Skiway                 |
  |  10146352 | us/montana/discover-ski                  | Discover Ski                     |
  |  10144882 | us/california/donner-ski-ranch           | Donner Ski Ranch                 |
  |  10146970 | us/new-york/hickory-ski-center           | Hickory Ski Center               |
  |  10146973 | us/new-york/holimont-ski-area            | Holimont Ski Area                |
  |  10146283 | us/minnesota/hyland-ski                  | Hyland Ski                       |
  |  10145911 | us/nevada/las-vegas-ski-snowboard-resort | Las Vegas Ski &amp; Snowboard Resort |
  |  10146977 | us/new-york/maple-ski-ridge              | Maple Ski Ridge                  |
  |  10146774 | us/oregon/mount-hood-ski-bowl            | Mt. Hood Ski Bowl                |
  |  10145949 | us/new-mexico/sipapu-ski                 | Sipapu Ski                       |
  |  10145952 | us/new-mexico/ski-apache                 | Ski Apache                       |
  |  10146584 | us/north-carolina/ski-beech              | Ski Beech                        |
  |  10147973 | canada/quebec/ski-bromont                | Ski Bromont                      |
  |  10146106 | us/michigan/ski-brule                    | Ski Brule                        |
  |  10145597 | us/massachusetts/ski-butternut           | Ski Butternut                    |
  |  10145117 | us/colorado/ski-cooper                   | Ski Cooper                       |
  |  10146917 | us/pennsylvania/ski-denton               | Ski Denton                       |
  |  10145954 | us/new-mexico/ski-santa-fe               | Ski Santa Fe                     |
  |  10146918 | us/pennsylvania/ski-sawmill              | Ski Sawmill                      |
  |  10145299 | us/illinois/ski-snowstar                 | Ski Snowstar                     |
  |  10145138 | us/connecticut/ski-sundown               | Ski Sundown                      |
  |  10145598 | us/massachusetts/ski-ward                | Ski Ward                         |
  +-----------+------------------------------------------+----------------------------------+
  </code></pre>
  
  <p>I'm at a complete loss as to why the query using fulltext search is not working. I'm hoping that some MySQL expert out there can point out the error in our query.</p>
  
  <p>Thanks in advance for your help!</p>