CODEWITHSUNDEEP
mysqlsqlfull-text-searchleft-joininnodb
David
David

Reputation: 3348

INNODB FULLTEXT search with JOIN: search term on different tables

I used to use the following query with MyISAM:

SELECT *, table2.columnx 
FROM table1 
    LEFT JOIN table2 ON table1.columnx_id = table2.id 
WHERE MATCH(table1.columny, table2.columnx) AGAINST("+key* +word*" IN BOOLEAN MODE);

This makes a FULLTEXT search on multiple tables.

Now, I switched to INNODB and I got the following error message:

General error: 1210 Incorrect arguments to MATCH

I rewrote the query as follows to remove the error message and make it working:

SELECT *, table2.columnx 
FROM table1 
    LEFT JOIN table2 ON table1.columnx_id = table2.id 
WHERE MATCH(table1.columny) AGAINST("+key* +word*" IN BOOLEAN MODE) 
      OR MATCH(table2.columnx) AGAINST("+key* +word*" IN BOOLEAN MODE);

This is working very well as long as all search terms key AND word are in the same table.

But if e.g. key is in table1 and word is in table1 I don't get any result.

How can I solve that problem?

Upvotes: 1

Views: 155

Answers (1)

David
David

Reputation: 3348

As described by @Akina this doesn't seem to be possible because INNODB doesn't allow FULLTEXT search on two tables.

I'll create a third table only for the FULLTEXT search and fill that with the data from both tables.

Upvotes: 1

Related Questions