MySQL - How to get search results with accurate relevance
<p>I have revisited this problem many times, and I have never really found a proper answer.</p>
<p><strong>Is it possible to perform a MySQL search which returns ACTUAL accurately sorted results by relevancy?</strong></p>
<p>I am trying to create an ajax search form which makes suggestions as the user types into an input field, and have found no decent solution to this using only pure MySQL queries. I know there are search servers available such as ElasticSearch, I want to know how to do it with a raw MySQL query only.</p>
<hr />
<p>I have a table of school subjects. There are less than 1200 rows and this will never change. Let's perform a basic FULLTEXT search where the user starts typing &quot;Bio&quot;.</p>
<p><strong>Query (&quot;Bio...&quot;) - FULLTEXT BOOLEAN MODE</strong></p>
<pre><code>SELECT name, MATCH(name) AGAINST('bio*' IN BOOLEAN MODE) AS relevance
FROM subjects
WHERE MATCH(name) AGAINST('bio*' IN BOOLEAN MODE)
ORDER BY relevance DESC
LIMIT 10
</code></pre>
<p><strong>Results</strong></p>
<pre><code>name                                  |  relevance
--------------------------------------------------------
Biomechanics, Biomaterials and Prosthetics  |  1
Applied Biology                             |  1
Behavioural Biology                         |  1
Cell Biology                                |  1
Applied Cell Biology                        |  1
Developmental/Reproductive Biology          |  1
Developmental Biology                       |  1
Reproductive Biology                        |  1
Environmental Biology                       |  1
Marine/Freshwater Biology                   |  1
</code></pre>
<p>To show how bad these results are, here is a comparison with a simple <code>LIKE</code> query which shows all the more relevant results which weren't shown:</p>
<p><strong>Query (&quot;Bio...&quot;) - LIKE</strong></p>
<pre><code>SELECT id, name
WHERE name LIKE 'bio%'
ORDER BY name
</code></pre>
<p><strong>Results</strong></p>
<pre><code>name                                  |  relevance
--------------------------------------------------------
Bio-organic Chemistry                       |  1
Biochemical Engineering                     |  1
Biodiversity                                |  1
Bioengineering                              |  1
Biogeography                                |  1
Biological Chemistry                        |  1
Biological Sciences                         |  1
Biology                                     |  1
Biomechanics, Biomaterials and Prosthetics  |  1
Biometry                                    |  1
</code></pre>
<p>And already you see how many subjects are not suggested, even though these are more likely what the user will be looking for.</p>
<p>The problem with using <code>LIKE</code> however, is how to search across multiple words and in the middle of words like <code>FULLTEXT</code> does.</p>
<p>The basic ordering I would want to implement is something like:</p>
<ol>
<li>First words starting with the search term</li>
<li>Second words starting with the search term</li>
<li>Words where the term is not at the start of the words</li>
<li>Everything generally alphabetical if not further relevant</li>
</ol>
<hr />
<p>So my question is, how does one go about getting a sensibly sorted list of suggestions for the user with a MySQL search across multiple words?</p>