By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
459,677 Members | 1,218 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 459,677 IT Pros & Developers. It's quick & easy.

making a table with unique entries

P: n/a
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?

TIA

- Nicolaas

Oct 10 '05 #1
Share this Question
Share on Google+
1 Reply


P: n/a
windandwaves wrote:
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?


You should read about index prefixes here:
http://dev.mysql.com/doc/mysql/en/create-index.html

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.

Regards,
Bill K.
Oct 10 '05 #2

This discussion thread is closed

Replies have been disabled for this discussion.