1

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`);
SAVAFA
  • 818
  • 8
  • 23
  • 3
    Already answered here: [http://stackoverflow.com/questions/9393234/mysql-unique-field-need-to-be-index](http://stackoverflow.com/questions/9393234/mysql-unique-field-need-to-be-index) – Fuujin Feb 08 '16 at 10:45
  • Thanks. So, only "Index" indexing is redundant, right? – SAVAFA Feb 08 '16 at 10:51
  • as I understand it: yes. Here is even more information: [see here](http://stackoverflow.com/questions/707874/differences-between-index-primary-unique-fulltext-in-mysql) – Fuujin Feb 08 '16 at 11:46

1 Answers1

1

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.

Rick James
  • 135,179
  • 13
  • 127
  • 222
  • 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. – SAVAFA Feb 10 '16 at 20:04
  • 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%'`. – Rick James Feb 11 '16 at 05:55
  • Thanks. Can you also take a look at this question of me: http://stackoverflow.com/questions/35267528/fulltext-indexing-on-myisam-single-column-vs-multiple-column-indexing – SAVAFA Feb 12 '16 at 19:32