Fulltext Search - Index & Search on columns

[natasha_sharma]

I have a table mytable, there are 5 text variables that i want to do full text index on. So that i can use any of these variables as and when i need. Sometimes I want to use all these 5 Text variables AND sometimes I just want to use any one or two of these for Full text searching.

I am using below Query:
 

ALTER TABLE mytable ADD FULLTEXT INDEX my_fts (text1, text2, text3, text4, text5);

Usage:
 

select MATCH(text1, text3 ) AGAINST ( ' blue widgets ' IN BOOLEAN MODE) as score from mytable;

Error: "Can not find index matching Index columns"

but when i make query like this:
 

select MATCH(text1, text2, text3, text4, text5 ) AGAINST ( ' blue widgets ' IN BOOLEAN MODE) as score from mytable;

There is not error. It works fine.

1) SO, Can I just not use One single or two Text variables for Full Text Searching?

2) If no, then what is the solution? If i want to keep full text index on all 5 variables, but want to search Against any one of them?



 

[natasha_sharma]

Anyone?

[Psycho]

No idea. I am unable to reproduce that error when using only some of the fields defined in the fulltext index. But, based on the comments in this post, the search must use the same columns in the same order as defined in the index.

 

However, I don't see any reason why you can't define multiple fulltext indexes for the same table using different combinations of fields.