Calculate MATCH() AGAINST() scores from UNIFIED QUERY not FOR EACH TABLE I am trying to have a score for entire section of SELECT statements

SELECT *,MATCH(`result`) AGAINST('keyword') as `score` FROM `table1` WHERE MATCH(`result`) AGAINST('keyword')
UNION
SELECT *,MATCH(`content`) AGAINST('keyword') as `score` FROM `table2` WHERE MATCH(`content`) AGAINST('keyword')
UNION
SELECT *,MATCH(`text`) AGAINST('keyword') as `score` FROM `table3` WHERE MATCH(`text`) AGAINST('keyword')


In such case scores are per table + they are not ordered by relevance

But I tried this method, which is working but is not worth of production

SELECT * FROM (
    SELECT *,MATCH(`result`) AGAINST('keyword') as `score` FROM `table1` WHERE MATCH(`result`) AGAINST('keyword')
    UNION
    SELECT *,MATCH(`content`) AGAINST('keyword') as `score` FROM `table2` WHERE MATCH(`content`) AGAINST('keyword')
    UNION
    SELECT *,MATCH(`text`) AGAINST('keyword') as `score` FROM `table3` WHERE MATCH(`text`) AGAINST('keyword')
) as `combined` ORDER BY `score` DESC


Above code is disliked because scores there are per table, they are joined and ordered. A bad approach.

So I tried to MATCH() AGAINST() for data in TOP LEVEL SELECT as well this. (DIDN'T WORK)

SELECT *,MATCH(`data`) AGAINST('keyword') as `good_score` FROM (
        SELECT *,`result` as `data`,MATCH(`result`) AGAINST('keyword') as `score` FROM `table1` WHERE MATCH(`result`) AGAINST('keyword')
        UNION
        SELECT *,`content` as `data`,MATCH(`content`) AGAINST('keyword') as `score` FROM `table2` WHERE MATCH(`content`) AGAINST('keyword')
        UNION
        SELECT *,`text` as `data`,MATCH(`text`) AGAINST('keyword') as `score` FROM `table3` WHERE MATCH(`text`) AGAINST('keyword')
    ) as `combined` ORDER BY `good_score` DESC


Above statement is perfect for me, but it does not work because data column is created on-the-fly and it does not support to have a FULLTEXT INDEX.

My question is how to proceed to get my engine working.


Can you somehow make data a FULLTEXT
Is there any way to make it work other than IN BOOLEAN MODE that does not support scores
Is there an approach to this entire topic that would make it work? Creating a temporary table does not solves this, 50% rule of MATCH() AGAINST() makes a query return 0 results, but there are many
Maybe there is a something little that I miss?
Creating a VIEW does not work either, MySQL does not support INDEX-es on VIEWs.
Maybe it is a good idea to use IN BOOLEAN MODE and create scoring manually?


I have been working for this issue for more than two days. So I kindly ask for some help.
Thanks.
Perhaps you could record the following from the three(3) tables


table name
the column from the table name
FULLTEXT index on the column


Here is the code:

DROP TABLE IF EXISTS combined_data;
CREATE TABLE combined_data
(
    source_table VARCHAR(64),
    source_id INT NOT NULL,
    data TEXT NOT NULL,
    FULLTEXT (data)
) ENGINE=MyISAM;
--
ALTER TABLE combined_data DISABLE KEYS;
--
INSERT INTO combined_data (source_table,source_id,data) VALUES
SELECT 'table1',id,`result` FROM table1 WHERE MATCH(`result`) AGAINST('keyword');
--
INSERT INTO combined_data (source_table,source_id,data) VALUES
SELECT 'table2',id,`content` FROM table1 WHERE MATCH(`content`) AGAINST('keyword');
--
INSERT INTO combined_data (source_table,source_id,data) VALUES
SELECT 'table3',id,`text` FROM table1 WHERE MATCH(`text`) AGAINST('keyword');
--
ALTER TABLE combined_data ENABLE KEYS;


Now you can run a single query against the one table

SELECT *,MATCH(`data`) AGAINST('keyword') as `good_score`
FROM combined_data
ORDER BY `good_score` DESC;


Give it a Try !!!