How to add a FULLTEXT field to an existing Table with FULLTEXT fields

Databases
1

Hi,

We have Table which has many fields with FULLTEXT Index on them.
We want to add a new Field to this Table which is also FULLTEXT and thus searchable via same search.

So we added this new column via:
ALTER TABLE swd ADD COLUMN phone VARCHAR(20);
and then added his:
ALTER TABLE swd ADD FULLTEXT (phone);

But when searching with this new filed included in the search, that is via:

SELECT id, ix_id, title, descpt, category FROM swd
WHERE MATCH (title, descpt, phone) AGAINST(‘$find’)

we get Error message:

Can’t find FULLTEXT index matching the column list

What to do to correct this?

Note that fields title, descpt had FULLTEXT added to them when the Table was created and the show create table lists like:

FULLTEXT KEY search_index (title,descpt),
FULLTEXT KEY phone (phone)

Thanks,
Dean

2

MySQL cannot use multiple indexes from one table in the same query. It always uses the best it finds. All fulltext columns must be in an compound index, i.e. you need a fulltext index on (title, descpt, phone).

3

So how do I create one fulltext index on (title, descpt, phone)?

Keeping in mind that massive amounts of Date are under title, descpt and field phone is the new one.

Thanks,

4 
ALTER TABLE foobar ADD FULLTEXT (col1, col2, col3...);
5

But col1, col2, aka: title, descpt
already have fulltext index on them.
So how to bring the new cold: phone
into the same FULLTEXT search as cols: title, descpt
which already have fulltext index on them?

Keeping in mind again that cols: title, descpt
are part of a live search system with massive amount of data in them.

6

I think I have the answer. We need to do this to add the new column phone to the columns already FULLINDEXed:

ALTER TABLE swd
DROP INDEX search_index,
DROP INDEX phone,
ADD FULLTEXT INDEX new_search_index (title,descpt, phone);

Question, will we loose any of the Searchable data in title,descpt?

7

FYI: I found the answer to this question.

8

And the answer is?..

9

42

:slight_smile:

3 Likes
10

Hi,
Sorry, the answer is what I posted in the message before that.
So 1st we need to DROP the current FULLTEXT Indexes and then put all the Cols that are to be FULLTEXT Indexed under the new FULLTEXT Index.
And NO, no data are lost.

11

This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.