How do I add index to a column in existing table?

I want to add MySQL FULL TEXT SEARCH in开发者_如何学运维dex to an existing table table1 to column called tags

Be careful when indexing with FULLTEXT indexes.

The default minimum word length is 4

MySQL has a list of words that are not imported into a FULLTEXT index. This list is called the stopword list

Click here for the list of over 600 words excluded from FULLTEXT indexes

This is what you must do before creating any fulltext indexes and bypass the stopwords:

Step 1) Create a stopword list with 'a','an','the'.

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

Step 2) Add these options to /etc/my.cnf

ft_stopword_lfile=/var/lib/mysql/stopwords.txt
ft_min_word_len=2

Step 3) service mysql restart

Step 4) Now, create the fulltext indexes you want.

If you created them already, drop them and create them again.

Give it a Try !!!

CREATE FULLTEXT INDEX `idxtags` ON `table1`(`tags`)

Full syntax at http://dev.mysql.com/doc/refman/5.5/en/create-index.html

The only reason I can imagine for doing that is because you have multiple tags stored in a single row. So I imagine your data looks something like this:

primary key    tags
--
1              tag1, tag2, tag3, tag4
2              tag1, tag5, tag7
3              tag4, tag13

Don't do that. Use a table of tags instead. Composite primary key would be (key, tag).

key    tag
--
1      tag1
1      tag2
1      tag3
1      tag4
2      tag1
2      tag5
2      tag7
3      tag4
3      tag13

Of course, since you didn't supply table structure or sample data, I could be completely off base. But based on the questions I read on SO every day, I'd be surprised.