Search code examples
mysqlsqlfull-text-search

MySQL fulltext in boolean mode doesn't match word (not disqualified word)


MySQL fulltext search doesn't match the word 'christoffer82' and its ilk with the root word 'christo' and I have no idea why. This login only appears once in the table (it doesn't appear in more than 50% of the results) and it is not even an excluded word.

NOK

SELECT
    usr.ID_USER AS userId
    , usr.USER_LOGIN AS userLogin
    , usr.EMAIL AS email
    , usr.MOBILE_PHONE AS mobilePhone
    , usr.FIRSTNAME AS firstName
    , usr.SURNAME AS surname
    , usr.USER_CREATED AS userCreated
    , usr.DATE_CREATED AS dateCreated
    , usr.USER_MODIFIED AS userModified
    , usr.DATE_MODIFIED AS dateModified
FROM
    BDT_USERS_DUMMY usr
WHERE 
    MATCH (usr.USER_LOGIN) AGAINST ('+christoffer82' IN BOOLEAN MODE)

OK

SELECT
    usr.ID_USER AS userId
    , usr.USER_LOGIN AS userLogin
    , usr.EMAIL AS email
    , usr.MOBILE_PHONE AS mobilePhone
    , usr.FIRSTNAME AS firstName
    , usr.SURNAME AS surname
    , usr.USER_CREATED AS userCreated
    , usr.DATE_CREATED AS dateCreated
    , usr.USER_MODIFIED AS userModified
    , usr.DATE_MODIFIED AS dateModified
FROM
    BDT_USERS_DUMMY usr
WHERE 
    MATCH (usr.USER_LOGIN) AGAINST ('+johan84' IN BOOLEAN MODE)

I tried everything I could find on stackOverflow


Solution

  • Solved. Problem was realy on my server and fulltext works :)

    my.ini

    increase => innodb_ft_max_token_size=50
    add => innodb_ft_num_word_optimize=5000
    add => innodb_ft_sort_pll_degree=10
    

    and rebuild index