MySQL Primary Keys and VARCHAR(255)

Question

I have a database using VARCHAR(255) as its primary key on tables and they look like GUIDs. Wouldnt an Int be better for performance?

Yes, since a PK is usually joined to an FK (with same type) in anotehr table, it's good to be small in size. An int (of size 4 or 8) is usually better than 255 for performance. — ypercubeᵀᴹ, Jan 11 '12 at 22:29
Additionally, GUIDs are not very good as PKs in InnoDB engine, as clustered indices work better with increasing values, not random ones (as the GUID produced). — ypercubeᵀᴹ, Jan 11 '12 at 22:31
See this [similar question and interesting answers](http://stackoverflow.com/questions/332300/is-there-a-real-performance-difference-between-int-and-varchar-primary-keys) — ypercubeᵀᴹ, Jan 11 '12 at 22:36

score 2 · Accepted Answer · answered Jan 11 '12 at 22:31

It depends on your storage engine, but generally speaking an int/bigint would be better. If you are using innodb, a uuid/guid is a bad choice for a primary key because of the way a clustered index works. read this blog to learn more about it. To sum it up, keys are stored by range and since uuids' are random they would make inserts and lookups less efficient since you would thrash the cache with reading and writing whole memory blocks for each row.

score 0 · Answer 2 · answered Jan 11 '12 at 22:30

0

Ints take less space on disk so you need less I/O when searching. As long as the range suits your need I would say that an int would be faster.

answered Jan 11 '12 at 22:30

Andreas Wederbrand

38,065
11
68
78

MySQL Primary Keys and VARCHAR(255)

2 Answers2