Relevance in a complex Fulltext query 1

[leegold2]

I want to have the query cited at the very bottom return a result sorted
by FullText Relevance. I'm thinking I would modify each SELECT
separately. For example maybe the first SELECT clause something like:

SELECT page.* FROM `page` LEFT JOIN `keywords` USING (`page_id`), MATCH
(`keywords`.`keyword_txt`)AGAINST ('$radio_keyword' IN BOOLEAN MODE) as
RELEVANCE FROM `page` WHERE MATCH (`keywords`.`keyword_txt`)AGAINST
('$radio_keyword' IN BOOLEAN MODE)

and then the same idea for the other two (2) SELECT clauses(?) I'm not
sure...here's the MYSQL (and php) query in question. Any help
appreciated:

$query =
"SELECT page.* FROM `page` LEFT JOIN `keywords` USING
(`page_id`) WHERE MATCH (`keywords`.`keyword_txt`)AGAINST
('$radio_keyword' IN BOOLEAN MODE)
UNION
SELECT page.* FROM `page` WHERE MATCH (`title`, `descrip`)
AGAINST ('$radio_keyword' IN BOOLEAN MODE)
UNION
SELECT page.* FROM `page` LEFT JOIN `url_pages` USING (`page_id`)
WHERE MATCH (`url_pages`.`page_url`) AGAINST ('$radio_keyword' IN
BOOLEAN MODE)";

 

[TonyGroves]

What you could do is, first, get all the relevant page_id's and their relevance ratings, and then join that union set to the page table, ordering the result by the relevance rating.

Something like:
[tt]
SELECT page.*,rel
FROM
(
SELECT
page.page_id pid,
MATCH(`keywords`.`keyword_txt`)
AGAINST('$radio_keyword' IN BOOLEAN MODE)
rel
FROM
`page`
LEFT JOIN `keywords` USING(`page_id`)
UNION
SELECT
page_id,
MATCH(`title`, `descrip`)
AGAINST('$radio_keyword' IN BOOLEAN MODE)
FROM `page`
UNION
SELECT
page.page_id,
MATCH(`url_pages`.`page_url`)
AGAINST('$radio_keyword' IN BOOLEAN MODE)
FROM
`page`
LEFT JOIN `url_pages` USING(`page_id`)
)
t
JOIN page ON t.pid=page.page_id
WHERE rel>0
ORDER BY rel
[/tt]
This requires MySQL 4.1.