MySQL Forums
Forum List  »  Full-Text Search

Problem with full-text search order by with UNION
Posted by: Brent Burgoyne
Date: September 30, 2008 11:17AM

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.

Options: ReplyQuote


Subject
Views
Written By
Posted
Problem with full-text search order by with UNION
7811
September 30, 2008 11:17AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.