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

Question

I want to add MySQL FULL TEXT SEARCH index to an existing table table1 to column called tags

Answer 1

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 !!!