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

Question

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`);

Already answered here: http://stackoverflow.com/questions/9393234/mysql-unique-field-need-to-be-index — Fuujin
– Fuujin, Commented Feb 8, 2016 at 10:45
as I understand it: yes. Here is even more information: see here — Fuujin
– Fuujin, Commented Feb 8, 2016 at 11:46

Rick James · Accepted Answer · 2016-02-09 19:22:16Z

2

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.

answered Feb 9, 2016 at 19:22

Rick James

144k15 gold badges144 silver badges255 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

SAVAFA Over a year ago

Thanks @rick. So, you're saying if I'm on MyISAM only FULLTEXT(x,y) would be enough for any fulltext searching? Also ordering point you mentioned was not completely clear for me.

Rick James Over a year ago

In a list of people ordered by (last_name, first_name), you can quickly find last_name='James' AND first_name LIKE 'R%', but it is much harder to find first_name = 'Rick' AND last_name LIKE 'J%'.

SAVAFA Over a year ago

Thanks. Can you also take a look at this question of me: stackoverflow.com/questions/35267528/…

Collectives™ on Stack Overflow

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

1 Answer 1

3 Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Linked

Related