CODEWITHSUNDEEP
sqlpostgresqlsqliteindexingprimary-key
Aelrond
Aelrond

Reputation: 3

SQL - Indexed Primary Key- Performance difference 64bit Int vs 8 char String

Say you design an SQL Table (postgresql or sqlite) and you have 2 Options for the Primary Key: (1) 64 bit Integer (autoincremented, indexed) (2) A String of 8byte (8 characters) whose first few letters are time dependant, hence sortable, also Indexed.

Would there be any performance / storage drawback to choosing the String version? Since they both take up 8 byte and are indexed and sortable by time (or order of insertion), they should also be the same speed for SELECT queries?

Upvotes: 0

Views: 742

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269503

Would there be any performance / storage drawback to choosing the String version?

Strings are much more complicated than they seem. For instance:

  • Are the characters ASCII or Unicode?
  • What is the collation?
  • Is the datatype char() or varchar()?

All of these things affect comparisons and cost -- in most databases. On the other hand, integers are simple. They simple are binary representations. Of course, they could be declared as unsigned versus signed, but that is one option versus many with strings.

SQLite uses an arcane data type system quite different from other databases (and from standard SQL), storing the type with the value rather than with the column definition. I don't know how that affects the performance of indexes in that database.

Upvotes: 1

Related Questions