It’s very easy to mess up your performance when you are using Full Text Index on MySQL. We were working on a system that used full text indices recently. The database had about a million records with long textual data fields. The search was taking around 5 minutes and our job was to optimize this!
We studied client’s requirements, dug deeper into the MySQL manuals and online information about fulltext indexing in MySQL, and decided to drop those indices! Realized that they were not needed in this case. Once we did that, added more relevant indices, and cleaned up a bit of queries, the whole page loaded up in less than 5 seconds!
So if you are considering fulltext indexing, make sure you read the documentation and design the solution carefully. If you don’t do it well, you will shoot a hole in your own foot!
Here I found a basic article for indexing techniques in MySQL http://saliljain.cutpage.com/?p=6