How to get scores from MySQL full text search

rubyonrails-talk
1

The mysql match function returns scores. I am using the mysql full text indexing (don't send the ferret sales pitch, I've seen it, its nice, I have my reasons*) but the results don't seem to be ranked in order of score. Is there anyway via the rails interface to get at the scores?

my code does this:       @searchtexts = SearchText.find(:all, :conditions => ['match(stext) against (? in boolean mode)', params[:searchtext].to_s], :limit => 20)

ideally i'd like to pass in an :order => "scores DESC" or something like that.

Anybody ever figure this out?

Mike Vargo

*reasons: If I have to scale to more than one mongrel server and I use ferret I have the problem of multiple servers looking at the same file system since the indexes are stored on the file system. Just like you put sessions into the db, you would have to figure this out. nfs mounts, file locking, rsync with a write master, it goes on. I don't deny it can't be done, but for my application, match() is much easier.

2

OK, so this was really lame on my part. But since I bugged everybody enough to post, i would post everybody my find.

The match does order descending by scores. My problem was due to the "in boolean mode". I did not understand the behavior of this feature correctly. What I thought the was: "In boolean mode works just like regular mode except you can use + and - as "has to" and "cannot have" modifies on words." That is not correct.

I got rid of the in boolean mode thing and the search works great ordering descending by score. Here is a couple of SQL examples I used to figure this out:

The non-boolean way. 9 rows returned

SELECT id, MATCH(stext) AGAINST ('bits') AS score FROM

search_texts WHERE MATCH(stext) AGAINST('bits') ;