CODEWITHSUNDEEP
mysqlsqljoinunion
Davit
Davit

Reputation: 1373

Rewrite SUBQUERY UNION SELECT into JOINS

In my query I ran into a problem where FULLTEXT INDEX cannot be asigned to a DERIVED TABLE, which appears to be normal.

Example demonstrates that data cannot become indexed as FULLTEXT, but content, text, and record columns are already indexed as FULLTEXT as they are in 'real' tables

SELECT `data` FROM SELECT(
   SELECT `content` as `data` FROM `table1`
   UNION SELECT `text` as `data` FROM `table2`
   UNION SELECT `record` as `data` FROM `table3`
) as `search`

In my actual code I use to SELECT MATCH() AGAINST() scores from tables and I UNION them together to get a score for each table, these UNIONS are subqueries and first level (main) SELECT then SELECTS those UNIONS as derived table and calculates a score from them, however this is not done. An error occurs saying that a reference is not having an INDEX. (Just like in an above example).

These is a reason why I decided to ask if it is possible to achieve my goal with JOINS.

Here is my actual (working) code:

SELECT *,MATCH(`data`) AGAINST('keyword' IN BOOLEAN MODE) as `relevance` 
FROM (                  
        SELECT CONCAT(`title`,' ',`content`) as `data`,`id`,'tmp_pages' as `table`, 
        MATCH(`title`,`content`) AGAINST ('keyword') AS `score` 
        FROM `tmp_pages` 
        WHERE MATCH(`title`,`content`) AGAINST ('keyword')
        UNION 
        SELECT CONCAT(`title`,' ',`content`) as `data`, `id`,'tmp_news' as `table`, 
        MATCH(`title`,`content`) 
        AGAINST ('keyword') AS `score` 
        FROM `tmp_news` 
        WHERE MATCH(`title`,`content`) AGAINST ('keyword')
        UNION 
        SELECT CONCAT(`title`,' ',`content`) as `data`,`id`,'tmp_comments' as `table`, 
        MATCH(`title`, `content`) 
        AGAINST ('keyword') AS `score` FROM `tmp_comments` 
        WHERE MATCH(`title`, `content`) AGAINST('keyword')
        UNION 
        SELECT CONCAT(`manufacturer`,' ',`model`,' ',`location`,' ',`other`,' ',`contact`) as `data`,`id`,'tmp_auction_auto' as `table`,
        MATCH(`manufacturer`,`model`,`location`,`other`,`contact`) AGAINST ('keyword') AS `score` 
        FROM `tmp_auction_auto` 
        WHERE MATCH(`manufacturer`,`model`,`location`,`other`,`contact`) 
        AGAINST ('keyword')
) as `search` ORDER BY `relevance` DESC

I would like to know if it is possible to turn this piece into JOIN type of query, plus make it function as expected. If so, I would kindly ask how to do it.

Upvotes: 0

Views: 713

Answers (1)

Eric Petroelje
Eric Petroelje

Reputation: 60498

I don't think you could do this as a JOIN since the tables are unrelated. However, you should be able to get rid of your error by simply omitting the relevance column from your outer query and ordering by score:

SELECT *
FROM (                  
        SELECT CONCAT(`title`,' ',`content`) as `data`,`id`,'tmp_pages' as `table`, 
        MATCH(`title`,`content`) AGAINST ('keyword') AS `score` 
        FROM `tmp_pages` 
        WHERE MATCH(`title`,`content`) AGAINST ('keyword')
        UNION 
        SELECT CONCAT(`title`,' ',`content`) as `data`, `id`,'tmp_news' as `table`, 
        MATCH(`title`,`content`) 
        AGAINST ('keyword') AS `score` 
        FROM `tmp_news` 
        WHERE MATCH(`title`,`content`) AGAINST ('keyword')
        UNION 
        SELECT CONCAT(`title`,' ',`content`) as `data`,`id`,'tmp_comments' as `table`, 
        MATCH(`title`, `content`) 
        AGAINST ('keyword') AS `score` FROM `tmp_comments` 
        WHERE MATCH(`title`, `content`) AGAINST('keyword')
        UNION 
        SELECT CONCAT(`manufacturer`,' ',`model`,' ',`location`,' ',`other`,' ',`contact`) as `data`,`id`,'tmp_auction_auto' as `table`,
        MATCH(`manufacturer`,`model`,`location`,`other`,`contact`) AGAINST ('keyword') AS `score` 
        FROM `tmp_auction_auto` 
        WHERE MATCH(`manufacturer`,`model`,`location`,`other`,`contact`) 
        AGAINST ('keyword')
) as `search` ORDER BY `score` DESC

Upvotes: 1

Related Questions