Column that matches in a fulltext index spanning multiple columns

Question

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.

what you want to achieve if you want to check match of each colum against something then do it in four different querry — Ankit Sharma, Apr 18 '12 at 10:54

score 1 · Accepted Answer · answered Apr 18 '12 at 13:05

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

Column that matches in a fulltext index spanning multiple columns

1 Answers1