Reputation: 4796
I try to add full text search to an existing table. When I tried:
alter table tweets add fulltext index(tags);
I got the error:
ERROR 1214 (HY000): The used table type doesn't support FULLTEXT indexes
what is the problem? How can I know what table type it is?
Upvotes: 7
Views: 5351
Reputation: 44373
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 may also want to preempt the stopword list.
Click Here for the Stop Words that FullText Indexing
Would Normally Ignore.
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) service mysql restart
Here is something else to consider:
You may not want to convert the table 'tweets' to MyISAM
.
1) If the InnoDB
table 'tweets' contains CONSTRAINT(s)
.
2) If the InnoDB
table 'tweets' is the parent of other InnoDB
tables with Foreign Key Constraints back to 'tweets'.
3) You cannot afford to have table-level locking of the 'tweets' table.
Remember, each INSERT
into the 'tweets' table will trigger a table-level lock if it were a MyISAM
table. Since it currently an InnoDB
table (which does row-level locking), the 'tweets' table can be INSERTed
into very quickly.
You many want to create a separate MyISAM
table, called tweets_tags
, with the same Primary Key of the 'tweets' table along with a TEXT
column called 'tags' the same as in the 'tweets' table.
Next, do an initial load of tweets_tags like this:
INSERT INTO tweets_tags (id,tags) SELECT id,tags FROM tweets;
Then, periodically (every night or every 6 hours), load new tweets into tweets_tags like this :
INSERT INTO tweets_tags (id,tags) SELECT id,tags FROM tweets WHERE id > (SELECT max(id) FROM tweets_tags);
Upvotes: 6
Reputation: 44161
If you want to use full text indexing you need to make sure your table's underlying engine is MyISAM. You can change this using ALTER TABLE tweets ENGINE = MYISAM;
Upvotes: 10