Mysql full text search error

Question

I am trying to add full-text search to an existing table. When I tried:

alter table tweets add fulltext index(tags); 

I got an error:

 ERROR 1214 (HY000): The used table type doesn't support FULLTEXT indexes 

What is the problem? How to find out what type of table it has?

Answer 1

This is how you check the table type:

 SELECT table_schema,engine FROM information_schema.tables WHERE table_name='tweet'; 

Only MyISAM supports FULLTEXT Indexes .

You can also preempt a list of stops.

Click here to stop words that FullText Indexing normally ignores.

You can override this as follows:

1) Create a text file in /var/lib/mysql , like this

 echo "a" > /var/lib/mysql/stopwords.txt<BR> echo "an" >> /var/lib/mysql/stopwords.txt<BR> echo "the" >> /var/lib/mysql/stopwords.txt<BR> 

2) Add this to /etc/my.cnf

 ft_stopword_file=/var/lib/mysql/stopwords.txt<BR> ft_min_word_len=2 

3) reboot mysql service

Here are some other things to consider:

You may not want to convert the table “tweets” to MyISAM .

1) If the tweets of the InnoDB tables contain CONSTRAINT(s) .

2) If InnoDB table 'tweets' is a parent of other InnoDB tables with foreign key restrictions back to “tweets”.

3) You cannot afford to lock the table of “tweets” at the table level.

Remember that each INSERT in the "tweets" table causes a table-level lock if it was a MyISAM table. Since this is currently an InnoDB table (which locks at the row level), the tweet table can be INSERTed very quickly.

You want to create a separate MyISAM table called tweets_tags , with the same main key of the table "tweets" along with a TEXT column called "tags", the same as in "tweets", the table.

Then bootstrap tweets_tags, for example:

 INSERT INTO tweets_tags (id,tags) SELECT id,tags FROM tweets; 

Then, periodically (every night or every 6 hours), upload new tweets to tweets_tags as follows:

 INSERT INTO tweets_tags (id,tags) SELECT id,tags FROM tweets WHERE id > (SELECT max(id) FROM tweets_tags); 

Answer 2

If you want to use full-text indexing, you need to make sure that your main engine in the table is MyISAM. You can change this using ALTER TABLE tweets ENGINE = MYISAM;