CODEWITHSUNDEEP
mysqlfull-text-searchmatchingfull-text-indexing
F21
F21

Reputation: 33401

Column that matches in a fulltext index spanning multiple columns

I am using the latest version of MySQL 5.5.

I have a fulltext index spanning multiple columns in a table generated specifically for fulltext search (other tables in the database uses innodb):

somedata_search
========
id
name
about
note
dislike

I have a fulltext index on all the columns except for ID. I am able to run fulltext searches using:

 SELECT * FROM account_search WHERE MATCH(name, about, note, dislike) AGAINST('mykeyword*' IN BOOLEAN MODE);

This all works fine, but is there a way to deteremine which column the match originates from for each row? If there are matches across columns in a row, I am happy to have just the first column.

Upvotes: 0

Views: 742

Answers (1)

Andreas Wederbrand
Andreas Wederbrand

Reputation: 40041

I don't think there is any "native" way of getting it but it is possible to do it anyway.

I'm not sure this is fast but it returns the correct data

select text_test.*,
       match(name) against ('dude' in boolean mode) as name_match, 
       match(info) against ('dude' in boolean mode) as info_match 
  from text_test 
 where match(name, info) against ('dude' in boolean mode);

http://sqlfiddle.com/#!2/5159c/1

Upvotes: 1

Related Questions