2

Assuming two columns in a MySQL (MyISAM) table:

title VARCHAR(1500)
url VARCHAR(155)

I want to create a composite unique index on both columns. I know of the limiting length feature for too long columns (like my title column). How does it play with compound indexes?

What I want is something like:

|--- url_value ---| + |--- title_value ---|
|--------155------| + |--------rest-------| <-- This should be the key length

By that I mean the key should have maximum length (I am using utf8 as encoding so I am limited to 333 characters allowed per column for unique indexes), where all 155 characters of column url and 178 characters of column title should be respected in the unique index. Sorry for the bad English. I tried to explain as well as I could. So bear with me if it seems confusing and I gladly will provide more information, if necessary.

Jonathan Hall
  • 75,165
  • 16
  • 143
  • 189
Aufwind
  • 25,310
  • 38
  • 109
  • 154

1 Answers1

2

I'm not exactly sure what your question is, so this may not really answer it, but one option for enforcing uniqueness on long fields like that is to instead enforce a unique constraint on a hash of the long fields, using MD5, or any other hashing algorithm that suits your needs. So...

title VARCHAR(1500)
url VARCHAR(155)
title_md5 VARCHAR(16)

You could create a trigger that automatically populates title_md5 every time title is updated. Then you put your unique index on (url,title_md5).

Of course this has some drawbacks. There is more overhead in the actual table--although much of that can be offset by the smaller/more efficient index. Also, indexing title_md5 doesn't make searching the actual title column any faster, so you may need a "duplicate" index on title, if you need to do searching on that.

Jonathan Hall
  • 75,165
  • 16
  • 143
  • 189