All geek questions in one place

MySQL Optional LEFT JOIN WITH MATCH

I have the following query that performs a full-text search on two columns in two different tables for the same search query in the 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 only run MATCH on tb1.comment, it works fine and I return the relevant search terms, but I want to execute it for both columns.

However, since another table is optional with a LEFT JOIN, it does not return anything if there are no matching identifiers. Any ideas on how to overcome this problem?

+4
source share
2 answers

I managed to figure out the following work, which seems to work perfectly and gives the desired results;

  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
+8
source

You cannot match multiple columns that are not in the same FULLTEXT index. From http://dev.mysql.com/doc/refman/5.6/en/fulltext-restrictions.html :

The MATCH () column list must exactly match the column list in the specific FULLTEXT index definition for the table, unless that MATCH () is in BOOLEAN MODE in the MyISAM table. For MyISAM tables, boolean mode searches can be performed on non-indexed columns, although they are likely to be slow.

In your particular case, you do not have an index consisting of exactly (tb1.comment, tb2.comment) -nor, so the match will never succeed.

To make this work, create a third table associated with these tables containing two comment fields, with indexed columns, and doing your match with what apppropriate JOIN .

+2
source

Source: https://habr.com/ru/post/1501004/

More articles:


All Articles