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?

  • 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 '15 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? – Sourav Ghosh Apr 20 '15 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 '15 at 11:35
  • So, I need root access? I can never do that on a shared hosting; right? :( – Sourav Ghosh Apr 20 '15 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 '15 at 11:42

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.