Full Text Search problem.

Aurasia

hello all!

I'm trying to tweak my search engine a bit and I've discovered a weird lil' glitch. My SCORE variable for every single entry is coming back as a '1'. It's not a floating number like the examples are in the MySQL manual ... why?

$query_articles = "SELECT *, MATCH(body, title, subject1, subject2, author) AGAINST ('$searchtopic' IN BOOLEAN MODE) AS score FROM data_array WHERE MATCH (body, title, subject1, subject2, author) AGAINST ('$searchtopic' IN BOOLEAN MODE) AND live_timestamp < $now AND `live` = 1 ORDER BY score DESC";

The order it returns didn't make sense. Articles with more instances of the searched word would come below others with less but then above others with less as well. So I printed the score with each article ... and it's '1' for every single entry ... any thoughts?

Thanks in advance!

Roger_Ramjet

If you read through the user notes on boolean full text search you will see an explanation for this

"Posted by Joe Laffey on January 5 2005 5:30pm [Delete] [Edit]

In response to the note above Posted by Adam George on December 13 2004 7:32pm:

In my tests it would seem that the score returned is an integer equal to the number of words matched. So if you match on 3 words the scores will range from 1 to 3. If you match only on one word, or only one word is matched in any document, then the scores would all be 1."

There is also the following post

"Posted by Richard on February 23 2005 8:11pm [Delete] [Edit]

In response to Joe Laffey and Adam George:
To enhance sorting of the results in boolean mode you can do the following:

SELECT id, text, MATCH (text) AGAINST ('word1 word2 word3')
AS score FROM table1
WHERE MATCH (text) AGAINST ('+word1 +word2 +word3' in boolean mode) order by score desc;

Using the first MATCH() we get the score in non-boolean search mode (more distinctive). The second MATCH() ensures we really get back only the results we want (with all 3 words). If you want to do 'any of the words' search only, it's better to use non-boolean search instead (unless you are using boolean in order to get rid of 50% treshold limit)."

Hope that answers your question. 🆒

Aurasia

Yes! That's it!

AHAa! Thank you so much Roger! I wanted to play with the code a bit before I replied since I had to do some other tweaking in order to get this to work. I can now run either my boolean search or the non-boolean and the non-boolean does actually bring back the scores in a much more suitable manner now.

Thanks so much! I love this place! The people here are so cool and helpful!