MySql Performance Question: MD5(value)

for security purpose I do some queries in this way:

SELECT avatar_data FROM users WHERE MD5(ID) ='md5value'

So, for example I have this entries:

-TABLE.users-
ID | avatar_data
39 | some-data

I do this query:

SELECT avatar_data FROM users WHERE MD5(ID) ='d67d8ab4f4c10bf22aa353e27879133c'

'd67d8ab4f4c10bf22aa353e27879133c' is the '39' value filtered by MD5.

I have a VERY large database with a lot of entries. I wonder if this approach might compromise the DB performance?

Because you are using a function on the column you want to search ( MD5(ID)= ), MySQL will have to do a full table scan.

While I am not sure your reason for doing a search like that, to speed things up, I can suggest you add another column with the processed ID data and index it.

So you should do:

SELECT * FROM user WHERE MD5_ID = 'd67d8ab4f4c10bf22aa353e27879133c'

With that query and without functional indexes, yes you would table-scan the whole thing. If you do that often, you may want to pre-compute the digest into a surrogate table or in another column, index and lookup directly.

Yes that would probably get very slow and it really doesn't add any security. MD5 of '39' is pretty easy to figure out. For a one way hash to be successful it needs to contain values that would be unknown to an attacker. Otherwise the attacker is just going to hash the value and you've not really accomplished anything.

You might consider posting more about what you're doing. For example: is this a web administration tool? Is it password protected? Etc.

if you want this kind of security you probably be better out if you save the passwords as a md5 hash. encoding id's dont realy give security