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.
1 Answer
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.