> > The problem is that he has it as a primary key, so he wants it to be
>
> > unique as well as indexed. The best solution (and MUCH MUCH MUCH
>
> > more efficient) would be to hash each of the four columns, and create
> > a primary key on that. Integer keys are much faster and memory-
> > efficient than string keys.
>
> Granted, but there's still the problem that the hash may not be unique,
> thus defeating the purpose of the primary key.
>
> I really need a longer primary key. Why is there a limit in the first
> place, and if there *is* a limit, why is it not configurable at
> runtime or
> database creation time?
> --
> Shankar.
If you use a good 64-bit hash, I doubt you'll run into any uniqueness problems. MySQL
will support that as a 64-bit BIGINT. You especially should not have any problems if you
hash each column, then do the primary key across the four hashes.
I'm not sure why there is a limit, but I'm also not sure why anybody in their right mind
would want a unique index that long :)
At a previous job, we tested a 32-bit hash function by running it against hundreds of
thousands of unique URL's stored in our database. We found one collision. A 64-bit hash
is billions of times better (4 billion, to be exact).
Steve Meyers
