Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question

migrated from stackoverflow.com Dec 17 '12 at 16:41

This question came from our site for professional and enthusiast programmers.

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

share|improve this answer
    
I have done similar thing before and it did not work. This one does not work either. Final SELECT from combined_data as good_score with MATCH() and AGAINST() gives a 0 result. I researched about this issue and found out that NON IN BOOLEAN MODE applies 50% rule, which by its nature somehow ignores results from one table when there is some mathematical relation with 50% of results. Still, thank you for suggestion, but I would like to hear some other ideas. Thanks again. –  dachints Dec 18 '12 at 8:31

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.