Is there any point in creating a second column optimized for FULLTEXT searches?

Question

Hi, the project I'm working on has for each column that needs to be searched a second column called "ft[columnname]" which has a FULLTEXT index and only this one is searched against.

This column contains an "optimized" text, that is automatically generated from the original column in the following way:

• The string is lowercased
• All accents are removed
• All punctuations and unsearchable characters are removed
• All duplicated words are removed
• All words are sorted from the longest to the shortest
• Other transformations that I don't really understand (related to combined-words)

For example "I like Pokémon, especially Pikachu!" becomes "especially pokemon pikachu like i".

Is there any (even a very tiny one) performance benefit? The data in the database never dynamically changes.

Answer 1

Honestly, you should not do it in 2nd column because by doing so, it implies you are using MyISAM storage engine for a production table. (or go ahead if you can afford to lose some data).

In fact, you do care about the performance, so you should consider using a capable full text search engine such as Sphinx: http://www.sphinxsearch.com/