Jump to content

Fulltext Search - Index & Search on columns


natasha_sharma

Recommended Posts

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?



 

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.