It's my understanding that a PK in MySQL is basically the same as a
unique index -- for MyISAM tables at least. For InnoDB it's a bit
different with InnoDB storing rows within the PK index (and inserting
a hidden PK if none is provided).

In short: I don't think you'll see any better performance by making
your PK be this BINARY column, instead of just using an
auto_increment PK column and having a UNIQUE constraint on your
BINARY column. I doubt you'll see significantly worse performance by
doing it either.

-JF


On Nov 8, 2006, at 6:02 PM, Michael Fischer wrote:

> On Wed, Nov 08, Jon Frisby wrote:
>
>> On Nov 7, 2006, at 6:47 PM, Michael Fischer wrote:
>>
>>>
>>> Any thoughts on using BINARY(N) or CHAR(N) as a primary key?
>>>
>>> Performance issues? In mysql, in general?
>>>
>>> Yes, in the context of the application, there is a very good
>>> reason for doing this, and not using an auto increment integer.
>
>> I'm curious to know why simply having a UNIQUE constraint on the
>> column is inadequate...
>>
>> -JF
>
> I'm concerned with performance, not integrity constraints.
>
> I was wondering, with respect to mysql internals, whether there
> was any substantial penalty to using CHAR or BINARY vs. INTEGER
> based primary keys. I imagine, though I have not probed the
> source code, that comparisons are done with bitwise-ands, or
> memcmp(3). For this of course, VARCHAR and VARBINARY fields
> would be a pain, because, from row to row, you couldn't
> accurately optimize for the width of the data in the key field.
> However, it might be perfectly reasonable to do so with fixed
> length CHAR or BINARY ones.
>
> Thanks.
>
> Michael
> --
> Michael Fischer Happiness is a config option.
> michael@visv.net Recompile and be happy.