MySQL Fulltext and scores.

Discussion in 'MySQL' started by rayhab, Apr 26, 2011.

  1. #1
    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
    ALTER TABLE main ADD FULLTEXT(col1, col2, col3);
    Code (markup):
    In my table i have
    main('Hello world / even more stuff', '', '')
    main('Hello world / even more stuff', 'testing', 'tester')
    main('Hello world / even more stuff', 'Hello world', 'Hello world')
    Code (markup):
    Now here is my query that gets the scores
    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
    Code (markup):

    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.

    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
    Code (markup):
    Any ideas of what the problem could be?
     
    rayhab, Apr 26, 2011 IP