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.
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.
- Alter the table to add an MD5 field:
alter table table1 add md5_of_column char(32)
- Update this field:
UPDATE table1 SET md5_of_column=md5(column);
- Add the unique index:
ALTER TABLE table1 ADD UNIQUE (md5_of_column)
- 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.
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.