CODEWITHSUNDEEP
mysqldatabase-designprimary-key
silkAdmin
silkAdmin

Reputation: 4810

Is there a recommended size for a Mysql Primary key

Each entry in my 'projects' table has a unique 32 characters Hash identifier stored using a varchar(32).

Would that be considered a bad practice to use this as the primary key ? Is there a recommended size, datatype for Primary keys ?

Upvotes: 5

Views: 10014

Answers (6)

Branko Dimitrijevic
Branko Dimitrijevic

Reputation: 52107

Dependstm ;)

Judging by your description, this field is intrinsic to your data and must be unique. If that really is the case, then you must make it a key. If you have child tables, consider introducing another, so called "surrogate" key simply to keep child FKs slimmer and possibly avoid ON UPDATE CASCADE. But beware that every additional index introduces overhead, especially for clustered tables. More on surrogate keys here.

On the other hand, if this key is not intrinsic to your data model, replace it with a smaller one (e.g auto-incremented integer). You'll save some disk space and (more importantly) increase the effectiveness of the cache.

Upvotes: 1

Andreas Wederbrand
Andreas Wederbrand

Reputation: 39981

This key is bad for several reasons.

  • One is addressed by @Eric in that every secondary index will contain those same 32 characters
  • Primary keys tend to be used in as look up from other tables and those tables also need to have those 32 characters, perhaps in there primary key and the same problem will arise again on those tables.
  • The biggest reason I can think of is performance. As you insert records of hash type you are basically inserting keys in random order and that in turn will eventually lead to a lot of page splits and pages that only between 50% and 90% filled. That leads to a unnecessary deep tree, longer search times, bigger table space and that the index takes more memory.

Upvotes: 0

the_marcelo_r
the_marcelo_r

Reputation: 1856

In database engines, one of the the most important items is the disk space. Keeping small and compact data is normally associated with good performance, by reducing the quantity of data that is transmitted and transferred by the database. If a table is going to have a few lines, there's no reason to define a PK of type INT; MEDIUMINT, SMALLINT or even TINYINT can be used instead (just as you would use DATE instead of DATETIME), it's all about keeping it succinct.

Upvotes: 0

Alex Howansky
Alex Howansky

Reputation: 53563

There's nothing inherently wrong with using this as the PKEY. If you've got many other tables using this as an FKEY, perhaps not. There's no one answer.

Also note, if you know it's always going to be exactly 32 chars, you should make it a CHAR(32) instead.

Upvotes: 0

Eric Petroelje
Eric Petroelje

Reputation: 60498

I would say yes, it's a bad idea to use such a large column for a primary key. The reason is that every index you create on that table will have that 32 character column in it, which will bloat the size of all the indexes. Bigger indexes mean more disk space, memory and I/O.

Better to use an auto-increment integer key if possible, and simply create a unique index on your hash identifier column.

Upvotes: 5

Jeremy
Jeremy

Reputation: 1972

Depends on your usage on how your primary key should be defined. I typically use an INT(11) for my primary keys. It makes it really easy for foreign keys.

I just saw your edit. I would personally use the int(11) with auto increment. Depending on your setup, this would allow for you to have other tables with foreign key restraints very easily. You could do the same thing with varchar but it has always been my understanding that int is faster than varchar especially with indexes.

Upvotes: 0

Related Questions