CODEWITHSUNDEEP
mysqlsqlperformancesqliteprimary-key
cdub
cdub

Reputation: 25701

Performance on PKs using int and char(32)

Does it make any difference performance wise to have a primary key of a database table (say Users with user_id being the PK), be an int or a char(32)?

I am wondering this for MySQL and SQLite.

The int would be an auto increment at 1,2,3,... and the char(32) would be a unique 32 alphanumeric characters.

Upvotes: 1

Views: 696

Answers (2)

CL.
CL.

Reputation: 180020

An index is more efficient when the table itself is stored in index order, because then it is not necessary to do a secondary lookup in the index to find a record. This is typically called "index-organized table" or "clustered index".

In SQLite, you must declare the PK as INTEGER PRIMARY KEY to get a clustered index.

MySQL supports both data types for a clustered index; the only difference is that char(32) occupies more space, and this might require more I/O.

Upvotes: 1

PascalKleindienst
PascalKleindienst

Reputation: 168

first of all I am not sure for SQLite but for MySQL it is better to use INT over CHAR(32) as a PK. It has to do with how MySQL builds up the index for the columns, so integers are always better than strings for PK.

Another thing is that CHAR(32) consumes much more space (32 Bytes = 256 bit) where an INT(10) only is 32 Bit long. That means that the CHAR(32) fields has much more different keys than the INT(10) field.

Upvotes: 1

Related Questions