Index of CHAR(32) vs BINARY(16) vs TEXT

MySQL & MariaDB Other MySQL® Questions
1

Things I don’t care about:

  • storage space (rows in the thousands, not millions)
  • security (this is not a password)

Things I do care about:

  • speed

So, I have a variable length bit of text that can be as small as 3 characters but will never be longer than what the TEXT type allows. And I need to make a comparison against this field. My idea is to md5 it in PHP and store it as either CHAR(32) or BINARY(16) (if I use the raw_output = TRUE parameter). But I could also skip the hash step and just store it in TEXT, though that seems like it’d be a much poorer index than one of the first two options.

Any thoughts?

Thanks!

2

TEXT will be bad. CHAR is better. BINARY is better if row length is an issue, but unreadable when debugging. I’d go CHAR.

Put an index on the field, too.