title: How do I get different scores for partially matching single-word searches on an InnoDB full text index?
body: <p>I've got the following table in a MySQL 5.6 with an InnoDB full text index.</p>
  
  <pre><code>CREATE TABLE `blacklist_entries` (
    `blacklist_entry_id` int(11) NOT NULL AUTO_INCREMENT,
    `name` varchar(100) NOT NULL,
    `insertat` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`blacklist_entry_id`),
    FULLTEXT KEY `ftk_b_n` (`name`)
  ) ENGINE=InnoDB AUTO_INCREMENT=660004 DEFAULT CHARSET=utf8
  </code></pre>
  
  <p>This is the data we are dealing with. All the entries in <code>name</code> are single words, sometimes with <code>-</code>, but never more than one word.</p>
  
  <pre><code>mysql&gt; select * from blacklist_entries where name like '%battle%';
  +--------------------+---------------------+---------------------+
  | blacklist_entry_id | name                | insertat            |
  +--------------------+---------------------+---------------------+
  |               4159 | battleground        | 2018-01-16 12:15:46 |
  |             604218 | battle              | 2018-01-16 12:18:59 |
  |             604219 | battlefield         | 2018-01-16 12:18:59 |
  |             604220 | battlefields        | 2018-01-16 12:18:59 |
  |             604221 | battles             | 2018-01-16 12:18:59 |
  |             660003 | abcbattle           | 2018-01-16 12:49:34 |
  +--------------------+---------------------+---------------------+
  </code></pre>
  
  <p>I would like to create a full text search this list that is able to sort by relevance, where a full match has a higher score than a partial match.</p>
  
  <p>When I run this query</p>
  
  <pre><code>select
      *,match(name) against ('battle battle* *battle' IN BOOLEAN MODE) as score
  from blacklist_entries where match(name)
      against ('battle battle* *battle' IN BOOLEAN MODE);
  </code></pre>
  
  <p>I get the following result.</p>
  
  <pre><code>+--------+---------------------+---------------------+--------------------+
  |     id | name                | insertat            | score              |
  +--------+---------------------+---------------------+--------------------+
  |   4159 | battleground        | 2018-01-16 12:15:46 |  17.11724281311035 |
  | 604218 | battle              | 2018-01-16 12:18:59 |  17.11724281311035 |
  | 604219 | battlefield         | 2018-01-16 12:18:59 |  17.11724281311035 |
  | 604220 | battlefields        | 2018-01-16 12:18:59 |  17.11724281311035 |
  | 604221 | battles             | 2018-01-16 12:18:59 |  17.11724281311035 |
  +--------+---------------------+---------------------+--------------------+
  </code></pre>
  
  <p>There are two issues with this.</p>
  
  <ul>
  <li>All the results have the same score, even the exact match</li>
  <li><p>It does not match from the right, and this line is missing</p>
  
  <pre><code>| 660003 | abcbattle           | 2018-01-16 12:49:34 |  17.11724281311035 |
  </code></pre></li>
  </ul>
  
  <p><strong>Can I change something in the query to convince MySQL to do score these matches differently?</strong> </p>
  
  <p>If that is not possible, are there viable alternatives, like implementing  my own scoring in a stored procedure, and iterate the full list again to at least identify the exact match to rank it higher?</p>
  
  <p>If none of these work, I would have to build the scoring in my application layer completely. Obviously that is going to cost additional resources, so I would like to avoid that.</p>