MySQL Fulltext and scores.

**rayhab** · Apr 26, 2011 08:29

Hello,
I am trying to understand why my fulltext queries are returning these scores, they don't make sense to me.

First off, here is my fulltext create

Code:

ALTER TABLE main ADD FULLTEXT(col1, col2, col3);

In my table i have

Code:

main('Hello world / even more stuff', '', '')
main('Hello world / even more stuff', 'testing', 'tester')
main('Hello world / even more stuff', 'Hello world', 'Hello world')

Now here is my query that gets the scores

Code:

SELECT
	MATCH(col1, col2, col3) AGAINST('+"hello world"' IN BOOLEAN MODE) as blnScore,
	MATCH(col1, col2, col3) AGAINST('hello world') as intScore

FROM
	main
WHERE
	MATCH(col1, col2, col3) AGAINST('hello world')
ORDER BY blnScore DESC, intScore DESC

You would think that the row with 'Hello world' in every column would have the highest score, since the index is on every column, but that is not the case.

Code:

main('Hello world / even more stuff', '', '') //Score - 6.48660516738892
main('Hello world / even more stuff', 'testing', 'tester') //Score - 6.34852838516235
main('Hello world / even more stuff', 'Hello world', 'Hello world') //Score - 3.5588550567627

Any ideas of what the problem could be?

**r937** · Apr 26, 2011 10:54

how many total rows in the table?

**rayhab** · Apr 26, 2011 11:36

Those are the only three rows.

**r937** · Apr 26, 2011 11:53

load your table with 3000 rows, and try again

the mysql manual specifically states that "For very small tables, word distribution does not adequately reflect their semantic value, and this model may sometimes produce bizarre results."

**rayhab** · Apr 26, 2011 11:56

Sorry, i meant in my test scenario(hello world) there was only three.
In my actually database is is over 1m

**rayhab** · May 9, 2011 07:36

r937, do you have any more recommendations?

**r937** · May 9, 2011 09:36

other than you shouldn't put any stock in the actual number, no

the "relevance" score is only supposed to return the most relevant rows first -- the number itself doesn't mean anything

**StarLion** · May 9, 2011 09:52

Also, the words "Hello" and "world" are in more than 50% of your rows (100%), and are therefore excluded from your search as per the Natural Language search rules... which makes your search somewhat irrelevant.