15

I need to store a very large number (tens of millions) of 512-bit SHA-2 hashes in a MySQL table. To save space, I'd like to store them in binary form, rather than a string a hex digits. I'm using an ORM (DBix::Class) so the specific details of the storage will be abstracted from the code, which can inflate them to any object or structure that I choose.

MySQL's BIGINT type is 64 bits. So I could theoretically split the hash up amongst eight BIGINT columns. That seems pretty ridiculous though. My other thought was just using a single BLOB column, but I have heard that they can be slow to access due to MySQL's treating them as variable-length fields.

If anyone could offer some widsom that will save me a couple hours of benchmarking various methods, I'd appreciate it.

Note: Automatic -1 to anyone who says "just use postgres!" :)

brian d foy
  • 124,803
  • 31
  • 200
  • 568
friedo
  • 64,458
  • 16
  • 112
  • 180

2 Answers2

21

Have you considered 'binary(64)' ? See MySQL binary type.

jeje
  • 3,062
  • 3
  • 24
  • 40
8

Use the type BINARY(64) ?

nos
  • 215,098
  • 54
  • 400
  • 488