MySQL fulltext search with word boundaries

Question

I've read some articles and issues, 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.

Consider a database table with the following three records in the column subject

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

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

SELECT 
    id, 
    subject, 
    MATCH(
        subject
    ) 
    AGAINST(
        'zoek*'
        IN BOOLEAN MODE
    ) 
    AS 
        score
FROM 
    Issues 
WHERE 
    MATCH(
        subject
    ) 
    AGAINST(
        'zoek*'
        IN BOOLEAN MODE
    )

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).

Instead the results of the query were only row 2 and 3 with exactly the same score (0.031008131802082062).

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

Answer 1

MySQL full-text search doesn't support suffixes.

To get the first row you would have to do a match against '*zoek*' which is currently not allowed.

The alternative is to use

SELECT id, subject
FROM Issues 
WHERE subject LIKE '%zoek%' 

Answer 2

As others advised, MySQL's FULLTEXT indexes do not support leading wildcards, and therefore cannot help in searching for suffixes.

However, the new ngram Full-Text Parser might help :

The built-in MySQL full-text parser uses the white space between words as a delimiter to determine where words begin and end, which is a limitation when working with ideographic languages that do not use word delimiters. To address this limitation, MySQL provides an ngram full-text parser (...).

An ngram is a contiguous sequence of n characters from a given sequence of text. The ngram parser tokenizes a sequence of text into a contiguous sequence of n characters.

As I have never used this feature, I cannot help further on this topic. Notice however:

Because an ngram FULLTEXT index contains only ngrams, and does not contain information about the beginning of terms, wildcard searches may return unexpected results.

Answer 3

Try this queries for different results:

1. Select all subject that starts with letter "z":
   SELECT ID, Subject FROM table_name WHERE Subject LIKE 'z%';

2. Select all subject that ends with letter "z":
   SELECT ID, Subject FROM table_name WHERE Subject LIKE '%z';

3. Select all subject containing the pattern "zoek":
   SELECT ID, Subject FROM table_name WHERE Subject LIKE '%zoek%';