MySQL fulltext search with word boundaries
<p>I've read some <a href="http://www.hackingwithphp.com/9/3/18/advanced-text-searching-using-full-text-indexes" rel="nofollow noreferrer">articles</a> and <a href="https://stackoverflow.com/questions/2716748/mysql-full-text-search-with-partial-words">issues</a>, but couldn't find a satisfying solution. I want to select related records from the database when a user fills in a form; on the same way the functionality works on this site when you ask a question.</p>

<p>Consider a database table with the following three records in the column <code>subject</code></p>

<pre><code>+---+---------------------------------------------------+
| 1 | Pagina aanmaken en beter doorzoekbaar maken       |
+---+---------------------------------------------------+
| 2 | Sorteerfunctie uitbreiden in zoek-en-boek functie |
+---+---------------------------------------------------+
| 3 | Zoek de verschillen tussen de pagina's            |
+---+---------------------------------------------------+
</code></pre>

<p>I start my search query with the word <code>zoek</code> so i want to query the most relevant results from the database on the term <code>zoek</code>. I came up with the following query:</p>

<pre><code>SELECT 
    id, 
    subject, 
    MATCH(
        subject
    ) 
    AGAINST(
        'zoek*'
        IN BOOLEAN MODE
    ) 
    AS 
        score
FROM 
    Issues 
WHERE 
    MATCH(
        subject
    ) 
    AGAINST(
        'zoek*'
        IN BOOLEAN MODE
    )
</code></pre>

<p>When i run this query i'd expected all the records to show and (probably, i don't know how specificity works in MySQL) ID 3 to display on top (because exact word match). </p>

<p>Instead the results of the query were only row <code>2</code> and <code>3</code> with exactly the same score (0.031008131802082062).</p>

<p>What do i need to change in my query to match appropriate records? Also considering that users can type in keywords or sentences.</p>