Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I am trying to create an InnoDB table that contains a VARCHAR column that can hold up to 3071 characters. I would like to enforce a UNIQUE constraint on the data of this column.

MySQL appears to enforce constraints using an index. In InnoDB, index sizes appear to be limited to 767 bytes - not nearly enough for the VARCHAR(3071) column that is holding the data.

Any thoughts on how to have the database enforce uniqueness of the data, without compromising on maximum data length or the usage of InnoDB?

share|improve this question

1 Answer 1

You do not want a gigantic gen_clust_index (Internal Clustered Index). That size is ungodly huge even for a secondary index.

You may have to resort to triggers or stored procedures to check for the key well in advance.

You could also think about performing an SHA1 function call using the VARCHAR(3071) field. SHA1 will return a 40-character field. This hash may be just what you need to index.

Suppose you have this

CREATE TABLE mytable
(
    id int not null auto_increment,
    txt VARCHAR(3071),
    primary key (id)
) ENGINE=InnODB;

and you want to make a UNIQUE index on txt. Try the SHA1 approach

CREATE TABLE mytablenew LIKE mytable;
ALTER TABLE mytable ADD txtsha1 CHAR(40);
ALTER TABLE mytable ADD UNIQUE KEY (txtsha1);
INSERT INTO mytablenew (id,txt,txtsha1)
SELECT id,txt,SHA1(txt) FROM mytable;

Then, count them

SELECT COUNT(1) FROM mytable;
SELECT COUNT(1) FROM mytablenew;

If the Counts are the Same, CONGRATULATIONS !!! Now you have a unique index of length 40. You can finish up with:

ALTER TABLE mytable RENAME mytableold;
ALTER TABLE mytablenew RENAME mytable;
DROP TABLE mytableold;

This could be more atomically as pointed out in the comments below:

RENAME TABLE mytable TO mytableold, mytablenew TO mytable;
DROP TABLE mytableold;

Perform this on whatever table you intend to have this big column. You have to remember to add the SHA1 of the data along with the data upon INSERT.

The odds of duplicate keys is 1 in 2 to the 160th power (that 1.4615016373309029182036848327163e+48. If I get the exact figure, I'll post it someday).

Give it a Try !!!

share|improve this answer
    
+1 This is basically a very good idea! I would hovewer combine it with a trigger that would check if two digests are the same, the content is the same too, exactly how a HashMap in Java works... –  ppeterka Oct 19 '12 at 7:38
1  
Rolando -- I have many quibbles: (1) sha1 should be ascii, not utf8. (2) sha1 could be BINARY(20) if you use HEX() and UNHEX(). (3) to make the rename atomic, with no downtime, do RENAME TABLE mytable TO mytableold, mytablenew TO mytable. Then DROP TABLE mytableold after you are satisfied. (4) The odds quoted is for a single row. (5) 2**64 is wrong -- it's 2**160. (6) the odds for a table is about: "There is one chance in 2**53 that a table with 2**53 rows will have a dup sha1." (6a) You are more likely to get by an asteroid while collecting on a mega-lottery. –  Rick James Oct 22 '12 at 23:33
    
@RickJames all points noted. Please excuse my bad math for point #5, it is 2^160. I adjusted #3 in my answer. –  RolandoMySQLDBA Oct 23 '12 at 2:42
    
Guys, the odds you present assume: 1. SHA has a perfect distribution; and 2. input is perfectly random. SHA does not have a prefect distribution. Nor does any other hashing algorithm. Input is not perfectly random, and although SHA, as other digests, cause for huge changes in output for any minor change in input, it is perfectly possible that some sets of inputs would make for the same output, and that those inputs have some systematic connection between them. Now, I'm mostly babbling here, since the odds are very low; but still, one should be wary. –  Shlomi Noach Oct 23 '12 at 12:36
    
@ShlomiNoach hashing keys can be drudgery. At this rate, even the PASSWORD function would be acceptable ( palominodb.com/blog/2011/12/04/hashing-algorithm-mysql-password ) –  RolandoMySQLDBA Oct 23 '12 at 14:55

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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