1

I have a database with a fulltext column name. I made a query

SELECT name, MATCH (name) AGAINST ('breaking bad') AS score
FROM files
WHERE MATCH (name) AGAINST ('breaking bad');

So that I can see matching score of results. I saw,

name
----------------------------------------------------  score-----------
Breaking Down.mp3-----------------------------------  5.59644222259521
Breaking Benjamin - Until The End.mp3---------------  5.53353023529053
aaf-breaking.bad.s03e10.fly.English.srt-------------  5.47201633453369
Breaking Bad Season 3 Episode 03 - I.F.T..avi-------  5.47201633453369

I thought row containing both breaking and bad will score higher. Can anyone please explain this behavior?

UPDATE

I got this. It is ignoring bad as it is less than 4 characters. Can anyone suggest how I can include that in my search also?

5
  • I recently answered a related (but not the same) question: dba.stackexchange.com/a/96847/30545 including some best practices and common issues with FULLTEXT.
    – jynus
    Apr 20, 2015 at 11:27
  • @jynus, that is a beautiful post. I know that Boolean mode can be used to return keywords occurring in more than 50% columns. But have you any idea about this minimum 4 character restriction? How to change ft_min_word_len? Apr 20, 2015 at 11:32
  • (Assuming MyISAM) set ft_min_word_len = 3 on your my.cnf/my.ini, one the [server] or [mysqld] section and restart the server. You may have to recreate your indexes to take effect. @SouravGhosh
    – jynus
    Apr 20, 2015 at 11:35
  • So, I need root access? I can never do that on a shared hosting; right? :( Apr 20, 2015 at 11:38
  • @SouracGhosh Unless you can change somehow the configuration of the server, I'm afraid not- send a support to your hosting to see if they can do something about it.
    – jynus
    Apr 20, 2015 at 11:42

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Browse other questions tagged or ask your own question.