CODEWITHSUNDEEP
mysqlsqlfull-text-search
PRNDL Development Studios
PRNDL Development Studios

Reputation: 3300

Match() Against() using joined columns

I have 3 tables, t1, t2, and t3 as follows:

t1
___
t1id
name
address
type

t2
___
t2id
title

t3
____
t3id
t1id
t2id

I have a full-text index on t1 covering name, address, and type.

What do I have to do to make the following query work? Just adding a full-text index to t2.title won't work, will it? 

SELECT t1.id, t1.name,MATCH(t1.name,t1.address,t1.type,t2.title) AGAINST ('query') as rank 
FROM t1
LEFT OUTER JOIN t3 ON t1.t1id = t3.t1id
LEFT OUTER JOIN t2 ON t3.t2id = t2.t2id
WHERE MATCH(t1.name,t1.address,t1.type,t2.title) AGAINST('query' IN NATURAL LANGUAGE MODE WITH QUERY EXPANSION) 
HAVING rank > 0.2 
ORDER BY rank DESC

http://sqlfiddle.com/#!2/a0a59/7

Upvotes: 0

Views: 149

Answers (1)

eggyal
eggyal

Reputation: 125955

As documented under Full-Text Restrictions:

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. Boolean-mode searches can be done on nonindexed columns, although they are likely to be slow.

You can instead combine multiple MATCH() expressions:

SELECT   t1.id, t1.name,
         MATCH(t1.name, t1.address, t1.type) AGAINST ('query')
       + MATCH(t2.title                    ) AGAINST ('query')
      AS rank
FROM     t1 LEFT JOIN t3 USING (t1id)
            LEFT JOIN t2 USING (t2id)
WHERE    MATCH(t1.name, t1.address, t1.type) AGAINST ('query')
       + MATCH(t2.title                    ) AGAINST ('query')
       > 0.2
ORDER BY rank DESC

Upvotes: 1

Related Questions