SAVAFA February 2016

MySQL Unique, Index, and Fulltext indexing. All together of one is enough?

I wanted to have one specific column be unique and also optimized for searching and sorting. Meanwhile the same column should have fulltext search capability. Should I add all three indexing types (Unique, Index, and Fulltext) together on this column or not?

Please help and thanks.

--Edit

Thanks @Fuujin for the quick comment.

What if I have the following indexing, no need for adding "Index" indexing anymore on neither of them, right?

ALTER TABLE `mytable` ADD UNIQUE (`column_1`, `column_2`);

Answers


Rick James February 2016

One column:

UNIQUE(x)
FULLTEXT(x)

Adding INDEX would be redundant, since UNIQUE is an INDEX.

Two columns:

UNIQUE(x,y)  -- the order depend on what your queries look like (see below)
FULLTEXT(x,y)
FULLTEXT(x), FULLTEXT(y) -- may need these (see below)

WHERE x > 5 AND y = 2 needs (y,x) order

If you search only one of the two columns, such as MATCH(x) AGAINST(...), then InnoDB, but not MyISAM, needs the extra FULLTEXT indexes.

Post Status

Asked in February 2016
Viewed 3,140 times
Voted 8
Answered 1 times

Search




Leave an answer