MySql Performance Question: MD5(value)

Question

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?

Answer 1

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'

Answer 2

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.