making a table with unique entries

Do you have a question? Post it now! No Registration Necessary.  Now with pictures!

Threaded View
Hi Gurus

I am trying to make a table with unique entries.  Problem is, the entries
are upto 1000 characters long.  Can I still apply a unique index and how and
if so, what field type should I choose?


- Nicolaas

Re: making a table with unique entries

windandwaves wrote:
Quoted text here. Click to load it

You should read about index prefixes here:

You can create an index on a prefix of a long string datatype, and this
can be up to 1000 bytes if you use MySQL 4.1.2 or later.

However, indexes based on 1000-byte prefixes are large.  Your index
would take a lot of space, and maintaining it would be more expensive.
If you know that the unique portion of the strings are guaranteed to
occur in, say, the first 90 characters, you could define the index
prefix to be that long.  Storing the index will be more efficient, as
will be using the index for matching or sorting.

Alternately, create another column, to store a hash of the long string.
  Use the MD5() function for example to compute a hash value.  Then
establish the unique constraint on the column with hash values.  Sorting
would be a problem, though, since the order of the hash values won't be
the same as the order of the strings.  But if your only use for the
index is to enforce uniqueness, it would work.

Bill K.

Site Timeline