Problem with full-text search order by with UNION

[Brent Burgoyne]

I am developing a web application that works with two difference sources of data; however, from the users perspective it is all the same. How it works out I need to do a full-text search on the data from the two tables and display it as one result in order of search relevance. I am attempting this through using a UNION between the two queries with one order by. Here is an example of what I am doing:

SELECT 'table 1' AS table_name, MATCH (description) AGAINST ('keyword') AS relevance
FROM table_1
WHERE MATCH (description) AGAINST ('keyword')
UNION
SELECT 'table 2' AS table_name, MATCH (description) AGAINST ('keyword') AS relevance,
FROM table_2
WHERE MATCH (description) AGAINST ('keyword')
ORDER BY relevance DESC

The problem is relevance is always 0 in the second query of the UNION even though the MATCH/AGAINST is working in the WHERE clause. Example:

---------------------------------------------
| table_name | relevance |
---------------------------------------------
| table 1 | 7.635 |
| table 1 | 5.876 |
| table 1 | 2.953 |
| table 1 | 1.345 |
| table 2 | 0 |
| table 2 | 0 |
| table 2 | 0 |
| table 2 | 0 |
---------------------------------------------

But if I run each query separately:

---------------------------------------------
| table_name | relevance |
---------------------------------------------
| table 1 | 7.635 |
| table 1 | 5.876 |
| table 1 | 2.953 |
| table 1 | 1.345 |
---------------------------------------------

---------------------------------------------
| table_name | relevance |
---------------------------------------------
| table 2 | 6.315 |
| table 2 | 4.946 |
| table 2 | 3.756 |
| table 2 | 2.745 |
---------------------------------------------

Does anyone have any ideas? I am running on MySQL version 4.1.15.



Edited 1 time(s). Last edit at 09/30/2008 11:20AM by Brent Burgoyne.