Efficient way to store hashes in database ?

[Mem5]

Hi,

What is the best way (for fastest request / response time) to store hashes in a mysql database ?

I saw UNHEX() which convert hexa in binary, then the column can store only digits, which I think is faster.

What to you suggest ?

Thanks.

[undeath]

if you only need to store user passwords, store a base64 encoded string of the hash.

[Bitweasil]

I wouldn't use MySQL if "fastest request/response time" were a concern.

If you wish to do this, storing them as a binary blob with prepared statements is probably the best option.

However, I would seriously suggest looking at a key value store system for handling this type of workload. It's much better suited to it than a full SQL database.

[unix-ninja]

Encoding the hashes in base64 will not get you the fastest processing time for storage and retrieval. In-fact, it will hurt your stats for sure.

There are MANY factors to consider with MySQL, including the table engines, table structures, and usage of the tables and data.

If you want to gain a little performance in storage, don't convert the values to hex, use hex literals instead. For example:

Code:

insert into table (hex_col) values (0xHEXVALUE);

Additionally, depending on what this table will be used for and how the data will be used, you could try to optimize it by using memory tables to gain significant performance.

The thing to remember here, though, is that everything is relative.

[Mem5]

Thank you for your answer.

@unix-ninja : when you suggest :

Code:

insert into table (hex_col) values (0xHEXVALUE);

what is the type of the column 'hex_col' ?

[twitty]

As Bitweasil said, MySQL is too slow. Try key/value as he suggested. I myself would use at MongoDB because it already is very efficient and it also has very nice support and there are many examples/lines of code flying around. You can also look at memcacheDB, Redis or other NoSQL database.