List:General Discussion« Previous MessageNext Message »
From:Steve Meyers Date:October 22 2001 5:42pm
Subject:RE: Length limit of 500 on primary keys?
View as plain text  
> > 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


Thread
Length limit of 500 on primary keys?Shankar Unni21 Oct
  • RE: Length limit of 500 on primary keys?Chris Bolt21 Oct
    • RE: Length limit of 500 on primary keys?Steve Meyers21 Oct
      • Re: Length limit of 500 on primary keys?Shankar Unni22 Oct
        • RE: Length limit of 500 on primary keys?Steve Meyers22 Oct
          • Re: Length limit of 500 on primary keys?Jeremy Zawodny23 Oct
            • RE: Length limit of 500 on primary keys?Steve Meyers23 Oct
              • RE: Length limit of 500 on primary keys?Steve Meyers23 Oct
                • Re: Length limit of 500 on primary keys?Shankar Unni24 Oct
              • Re: Length limit of 500 on primary keys?Bill Adams23 Oct
                • RE: Length limit of 500 on primary keys?Steve Meyers23 Oct
              • RE: Length limit of 500 on primary keys?Don Read23 Oct
            • Re: Length limit of 500 on primary keys?Dan Nelson24 Oct
Re: Length limit of 500 on primary keys?Heikki Tuuri23 Oct