Binary vs Integer - as primary key?

views:

answers:

+1 Q:

Binary vs Integer - as primary key?

I was wondering if there are any obvious pros and cons one should be aware about, while choosing to use a Binary type as a primary key, while being RANDOMIZED.

Which means, new inserts will have random 4 bytes as their PK - versus having a sequence of type Integer?

I'm using MySQL/MyISAM.

Thanks, Doori Bar

+5 A:

If the primary key isn't sequential then insert performance for a clustered index will not be very good. It has to rearrange the index for almost every insert.

Jay 2010-10-27 21:17:10

Thanks for the fast response, I was wondering if this performance cost - is the same cost as one would pay, for using a sequential integer primary key, while having a randomized 4 bytes as an index?

Doori Bar 2010-10-27 21:31:42

@Doori: depends, if InnoDB and more fields, definitely less (rows are stored based on primary key / has a clustered index), for MyISAM, you'll have to test, and it depends on your actual table, but I think it's safe to say 2 indexes instead of 1 will have an overhead of some sorts.

Wrikken 2010-10-27 21:42:15

@Wrikken: So as I understand, it's a mandatory performance cost which I have to accept while having the requirement of a randomized index. Thanks for your clarification.

Doori Bar 2010-10-27 21:56:34

Indeed, randomized will mean somewhere in the system records or keys are stored in an order that is not the order of creation, resulting in having to 'shift' data after creation of a new random key, which always be more costly then appending the certain sequential one.

Wrikken 2010-10-27 22:31:41

Using Guids for primary keys also suffers from this as well. They're basically just random numbers as well. I avoid random numbers as a primary key because they're not guaranteed to be unique. You end up jumping through a lot of hoops to get what's already provided by using sequential integers (GUIDs).

Jay 2010-10-28 15:27:37

I've stumbled: "USING HASH" under MySQL... It's actually what I need to make things more efficient and suitable to the case?

Doori Bar 2010-10-29 00:15:06

ansaurus

tags:

views:

answers:

Binary vs Integer - as primary key?

related questions