Efficiently storing md5

[Fred Van Andel]

On 25 Jan 2002 07:05:32 +0800, [EMAIL PROTECTED] (Steven Roussey)
wrote:

Does anyone have a best practices for efficiently storing md5 hash
values in MySQL? 

--snip--

Md5 hash-- 16 bytes.
char(32) binary -- 32 bytes.
BIGINT  -- 8 bytes
--snip--

Or you can use base64, which uses 22 bytes per hash.  

What I use is the last 8 bytes of the hash and store it as a bigint.

I use the hash only for collision detection, 64 bits will allow over 4
billion entries before the odds of a single incorrect collision
reaches 50%.  Since my total database is in the 10's of millions I
have very little to worry about.  

Note: This reply was originally posted to mailing.database.mysql instead of
  this list.

FVA





-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Efficiently storing md5

[Steven Roussey]

Does anyone have a best practices for efficiently storing md5 hash
values in MySQL? 

Since it is a 32 character string of hex numbers, I originally stored
them in a char(32) binary column. But that is wasted space (by a factor
of 2). And of course, these things add up, both in data files and
indexes.

Md5 hash-- 16 bytes.
char(32) binary -- 32 bytes.
BIGINT  -- 8 bytes

My thought right now is to convert the md5 hash into two BIGINT numbers
and visa-versa.

High BIGINT = conv(left(md5,16),16,10)
Low BIGINT = conv(right(md5,16),16,10)

And reversing:

Md5 = concat(lpad(conv(high,10,16),16,'0'),lpad(conv(low,10,16),16,'0'))

I suppose there is some point (in terms of the number of rows) where
storing more efficiently outweighs the conversion functions, and I
assume that I'm past that point. Is there a better way to convert these?


Just a note: there is no 128-bit integer type and CONV() only works with
up to 64-bit 8-byte numbers anyway.

Sincerely,
Steven Roussey
http://Network54.com/?pp=e 




-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php