mysql boolean search

[webnoob]

Hi,
i am trying to get this query to work correctly. It works but when i use BOOLEAN the score it returns is just a 1 for each result vs a score with decimals.

when i take out BOOLEAN it shows the correct score.. also i am using a UNION as well.. everything works except the score it returns.. am i doing something wrong?

$querysearch = $db->query("
(SELECT id,topic,text, 1 AS result_type, MATCH (topic,text)
AGAINST ('" . implode(" ", $QLogic) . "' IN BOOLEAN MODE) AS score FROM news
WHERE MATCH(topic,text) AGAINST('" . implode(" ", $QLogic) . "' IN BOOLEAN MODE))

UNION

(SELECT id,question,answer, 2 AS result_type, MATCH (question,answer)
AGAINST ('" . implode(" ", $QLogic) . "' IN BOOLEAN MODE) AS score FROM faqs
WHERE MATCH(question,answer) AGAINST('" . implode(" ", $QLogic) . "' IN BOOLEAN MODE))
ORDER BY score DESC
");

[coopster]

Why are you using the IN BOOLEAN MODE modifier? You aren't using any operators by the looks of it.

(no operator)

By default (when neither + nor - is specified) the word is optional, but the rows that contain it are rated higher. This mimics the behavior of MATCH() ... AGAINST() without the IN BOOLEAN MODE modifier.

[dev.mysql.com...]

[webnoob]

i am, the boolean operators are passed thru $QLogic

i just found this in the manual which explains why its not sorting the results ranking higher at the top:

"Note that a boolean mode search does not auto-magically sort rows in order of decreasing relevance."

that defeats the purpose of using the scoring in fulltext when you use boolean which sucks.. there is no point in using boolean if the best results are going to be at the bottom.

[someone]

Some person posted the following message on mysql.com regarding getting the relevance of IN BOOLEAN MODE to work.

___________________________________

It should be noted in the documentation that IN
BOOLEAN MODE will almost always return a
relevance of 1.0. In order to get a relevance that is
meaningful, you'll need to:
<BR/><BR/>
SELECT MATCH('Content') AGAINST ('keyword1
keyword2') as Relevance FROM table WHERE MATCH
('Content') AGAINST('+keyword1 +keyword2' IN
BOOLEAN MODE) HAVING Relevance > 0.2 ORDER
BY Relevance DESC
<BR/><BR/>
Notice that you are doing a regular relevance query
to obtain relevance factors combined with a WHERE
clause that uses BOOLEAN MODE. The BOOLEAN
MODE gives you the subset that fulfills the
requirements of the BOOLEAN search, the relevance
query fulfills the relevance factor, and the HAVING
clause (in this case) ensures that the document is
relevant to the search (i.e. documents that score
less than 0.2 are considered irrelevant). This also
allows you to order by relevance.
<BR/><BR/>
This may or may not be a bug in the way that IN
BOOLEAN MODE operates, although the comments
I've read on the mailing list suggest that IN
BOOLEAN MODE's relevance ranking is not very
complicated, thus lending itself poorly for actually
providing relevant documents. BTW - I didn't notice
a performance loss for doing this, since it appears
MySQL only performs the FULLTEXT search once,
even though the two MATCH clauses are different.
Use EXPLAIN to prove this.