Redundant to have index + full text on same column?

Question

If I have a full text index is it redundant to have a regular index on the same column? I am asking this because I need to support 5.5 and 5.6 at the same time and am using a stored procedure to create the indexes and am wondering if I need both.

Answer 1

Not redundant, but rarely practical.

An INDEX has limitations, based on 255 characters. (There are too many wrinkles for me to go into detail.)

FULLTEXT is often used for big blocks of text.

Would you be looking at 'words' and doing = comparisons on the whole column? Seems strange.

Here's one trick for when I want to search for a phrase that messes up in FULLTEXT. Let's say I am looking for the title of your question, but I remember only 'index + full' (including the plus sign):

WHERE MATCH(col) AGAINST('+index +full')
  AND col LIKE '%index + full%'

The MATCH will quickly find only a few rows, but too many. Then the LIKE finish filtering. No plain INDEX is involved.