title: MySQL - Efficient search with partial word match and relevancy score (FULLTEXT)
body: <p>How can I do a MySQL search which will match partial words but also provide accurate relevancy sorting?</p>
  
  <pre><code>SELECT name, MATCH(name) AGAINST ('math*' IN BOOLEAN MODE) AS relevance
  FROM subjects
  WHERE MATCH(name) AGAINST ('math*' IN BOOLEAN MODE)
  </code></pre>
  
  <p>The problem with boolean mode is the relevancy always returns 1, so the sorting of results isn't very good. For example, if I put a limit of 5 on the search results the ones returned don't seem to be the most relevant sometimes.</p>
  
  <p>If I search in natural language mode, my understanding is that the relevancy score is useful but I can't match partial words.</p>
  
  <p>Is there  a way to perform a query which fulfils all of these criteria:</p>
  
  <ul>
  <li>Can match partial words</li>
  <li>Results are returned with accurate relevancy</li>
  <li>Is efficient</li>
  </ul>
  
  <p>The best I've got so far is:</p>
  
  <pre><code>SELECT name
  FROM subjects
  WHERE name LIKE 'mat%'
  UNION ALL
  SELECT name
  FROM subjects
  WHERE name LIKE '%mat%' AND name NOT LIKE 'mat%'
  </code></pre>
  
  <p>But I would prefer not to be using <code>LIKE</code>.</p>