Calculate MATCH() AGAINST() scores from UNIFIED QUERY not FOR EACH TABLE

Question

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.

Answer 1

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 !!!