MySQL Optional LEFT JOIN With MATCH

Question

I have the following query, which performs a full text search against two columns in two different tables for the same search term in a MySQL Innodb database;

SELECT Id, 
MATCH (tb1.comment, tb2.comment) AGAINST (+'search term' IN BOOLEAN MODE) AS Relevance
FROM tbl1
LEFT JOIN tb2 ON tb1.Id = tb2.Id
WHERE MATCH (tb1.comment, tb2.comment) AGAINST (+'search term' IN BOOLEAN MODE) 
HAVING Relevance > 0

If I perform the MATCH on just tb1.comment it works fine and I get back the relevant search terms, but I want to perform it against both columns.

However because the other table is optional with the LEFT JOIN it doesn't return anything, if there is no matching Ids. Any ideas on how to overcome this problem ?

score 8 · Accepted Answer · answered Sep 06 '13 at 21:15

8

I managed to figure out the following work around that appears to perform fine and give the results I desire;

    SELECT Id, 
    MATCH (tb1.comment) AGAINST (+'search term' IN BOOLEAN MODE) AS Relevance1,
    MATCH (tb2.comment) AGAINST (+'search term' IN BOOLEAN MODE) AS Relevance2
    FROM tbl1
    LEFT JOIN tb2 ON tb1.Id = tb2.Id
    WHERE (MATCH (tb1.comment) AGAINST (+'search term' IN BOOLEAN MODE) 
    OR MATCH ( tb2.comment) AGAINST (+'search term' IN BOOLEAN MODE))
    HAVING (Relevance1+Relevance2) > 0 
    ORDER BY (Relevance1+Relevance2) DESC

answered Sep 06 '13 at 21:15

neildt

5,101
10
56
107

Since you have `HAVING (Relevance1+Relevance2) > 0`, can't you simply omit the `WHERE MATCH ... OR MATCH ...`? Would the result not be the same? – Ivo Renkema Jan 25 '17 at 18:49
you can combine the match scores in the select `(MATCH (tb1.comment) AGAINST (+'search term' IN BOOLEAN MODE) + MATCH (tb2.comment) AGAINST (+'search term' IN BOOLEAN MODE)) AS Relevance` – jim smith Feb 01 '17 at 17:00

score 2 · Answer 2 · answered Sep 06 '13 at 20:50

2

You can't match against multiple columns that aren't in the same FULLTEXT index. From http://dev.mysql.com/doc/refman/5.6/en/fulltext-restrictions.html:

The MATCH() column list must match exactly the column list in some FULLTEXT index definition for the table, unless this MATCH() is IN BOOLEAN MODE on a MyISAM table. For MyISAM tables, boolean-mode searches can be done on nonindexed columns, although they are likely to be slow.

In your particular case, you don't have an index consisting of exactly (tb1.comment, tb2.comment)—nor can you—so the match can't ever succeed.

To get this to work, create a third table linked to those tables containing the two comment fields, with both columns indexed, and perform your match against that in an apppropriate JOIN.

answered Sep 06 '13 at 20:50

Jeremy Smyth

23,270
2
52
65

So by creating a third table I will need to copy the comment data to this table as well, so the comment data from tb1 and tb2 will be in tb3 as well ? – neildt Sep 06 '13 at 20:57
Yes, unless you normalize by putting the comments in this third table and removing them from the first two. You'd have to change your app to do this. Have a look at the relationship between the `film` and `film_text` tables in the `sakila` sample database. `film_text` contains each film's description, and is used to do FULLTEXT searches. – Jeremy Smyth Sep 06 '13 at 20:59
Or is it possible to create two fulltext searches against each table, and UNION the results together ? – neildt Sep 06 '13 at 21:01
You could do two `MATCH()AGAINST`s in the `WHERE` clause linked with an `OR`, but you'd lose the preferential sorting and multiple conditions (e.g. you can't have one column has `+val`, the other has `-val`, because the `OR` will ignore a negative result) that a single FULLTEXT index on both columns gives. – Jeremy Smyth Sep 06 '13 at 21:03

MySQL Optional LEFT JOIN With MATCH

2 Answers2

Linked