title: Match partial words using MySQL Fulltext search?
body: <p>I have the following database schema, as an example:</p>
  
  <pre><code>CREATE TABLE IF NOT EXISTS `items` (
    `id` int(11) unsigned NOT NULL,
    `irn` varchar(30) NOT NULL,
    `name` varchar(225) NOT NULL,
    `description` text,
    PRIMARY KEY (`id`),
    FULLTEXT KEY `name_desc_irn` (`name`,`description`,`irn`),
    FULLTEXT KEY `name` (`name`),
    FULLTEXT KEY `description` (`description`),
    FULLTEXT KEY `irn` (`irn`)
  ) ENGINE=MyISAM  DEFAULT CHARSET=utf8;
  </code></pre>
  
  <p>And the following data (assume the data here is simplified, in reality I have over 100,000 records)</p>
  
  <pre><code>id    name        irn          description     
  1     Widget A    ABC12345     testing ABC12345
  2     Widget B    ABC-12345    the ABC is great
  </code></pre>
  
  <p>And the following query:</p>
  
  <pre><code>SELECT
      items.id as id,
      items.irn as irn,
      items.name as name,
      (
          ((MATCH (irn) AGAINST ('12345')) * 5) + 
          ((MATCH (name) AGAINST ('12345')) * 4) + 
          ((MATCH (description) AGAINST ('12345')) * 3) + 
          (MATCH(name, description, irn) AGAINST ('12345'))
      ) AS relevance 
  FROM
      items 
  WHERE 
      MATCH(name, description, irn) AGAINST ('*12345' IN BOOLEAN MODE)
  HAVING
      relevance &gt; 0
  ORDER BY
      relevance DESC
  </code></pre>
  
  <p>The second result (irn of ABC-12345) is returned but not the first (ABC12345). Why does it not match ABC12345 but does match ABC-12345? Is there any way to have it match both using Fulltext search? If not, what's the most appropriate way to conduct this type of search where ABC12345 and ABC-12345 are both found when searching for 12345?</p>