5

We need to store many rows in a MySQL (InnoDB) table, all of them having a 8-byte binary string as primary key. I was wondering wether it was best to use the BIGINT column type (which contains 64-bit, thus 8-byte, integers) or BINARY(8), which is fixed length.

Since we're using those ids as strings in our application, and not numbers, storing them as binary strings sounds more coherent to me. However, I wonder if there are performance issues with this. Does it make any difference?

If that matters, we are reading/storing these ids using hex notation (like page_id = 0x1122334455667788). We wouldn't use integers in queries anyway, since we're writing a PHP application and, as you surely know, there isn't a "unsigned long long int" type, so all integers are machine-dependant size.

ItalyPaleAle
  • 7,185
  • 6
  • 42
  • 69

1 Answers1

2

I'd use the binary(8) if this matches your design.

Otherwise you'll always have a conversion overhead in performance or complexity somewhere. There won't be much (if any) difference between the types at the RDBMS level

gbn
  • 422,506
  • 82
  • 585
  • 676