1

Is it possible to create a UNIQUE FULLTEXT index? I am using a mysql diff tool that is complaining that I am using duplicate indexes. I have a column that needs to be unique but also used for full text search.

2

Since there is a limit on the length of the index, if you want to guarantee the uniqueness of the whole field's value, you may want to add a hash field of that varchar field.

Steps:

  1. Alter the table to add an MD5 field: alter table table1 add md5_of_column char(32)
  2. Update this field: UPDATE table1 SET md5_of_column=md5(column);
  3. Add the unique index: ALTER TABLE table1 ADD UNIQUE (md5_of_column)
  4. Either create a trigger, or do it at application level, to update this new field after each insert/update operation.

Note that this solution might have a light performance hit.

  • Good idea about adding a hash. BEFORE trigger or application change should be better as we need to ensure uniqueness based on that. – mysql_user Aug 29 '15 at 3:32
0

Chris, I don't think it is. You can always try. (https://dev.mysql.com/doc/refman/5.5/en/fulltext-search.html) FullText Index are different than other indexes, it is meant for text search using Mysql specific function like MATCH. It would not make sense to have a unique Index on a full text column where you would store text. I would create a "description" column varchar(200) for example and have this unique. If your text is not too large, you can store it as varchar(5000) or even bigger, but your index will be limited to the first 767 char. (An index key for a single-column index can be up to 767 bytes). You would need to define the length of your index :

create table table1 (id int, stringCol varchar(5000)
create unique index idx_stringCol on table1(stringCol,760)

You might need to make index length smaller if column is using unicode.

0

Yes, it is possible but they will be two different indexes.

create unique index idx_val on table(column);
create fulltext index ftidx_val on table(column);

but as @greenlitmysql has mentioned you might see performance issues later on as the data grows (and length of column)...

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.