The best way for storing hash in database

Question

I need to find out the best way from performance and storage point of view for storing the hash, for example, MD5. The current database is MariaDB in the future it could be Oracle. The table will contain hundreds of millions or billions record and each record should include one hash value, that could be used for the search. At this time for storing the hash I use varchar(32), but I think this type is not optimal. I also think about type like binary, char, also as I understand for stable sizes, like in hash, is better to used char instead of varchar and binary instead of varbinary. Also, I think about converting the hash to digits, will it be better? So what is the best way of storing the hash in Database?

Answer 1

The short answer is each datatype should be stored in the native format supported by the RDBMS.

This is for Oracle RAW(16) for the MD5 hash code.

You see this analogy - some have decided to store DATE columns using VARCHAR format. You get a database independence but you can't use any function provided for DATEcolumns by your RDBMS.

Anyway you should carefully consider why use a HASH column in a database.

If it is a substitute to quickly recognise a change of row columns, it could save you a lot of coding and processing.

To use a hash code as a key, try to find at least one reason, that you will profit from a HASH key, compared with a use of a sequence generated key.

Be carefull not to use HASH as a key only because this is recomended from some source before you see in your own imlementation the described positive effect.