MySQL hex() vs unhex()

Question

When storing binary data in MySQL I use the hex() and unhex() functions. But there are two ways I can search on binary data:

Method 1

select * from tbl
where
    id=unhex('ABCDABCDABCDABCDABCDABCDABCDABCD')

Method 2

select * from tbl
where
    hex(id)='ABCDABCDABCDABCDABCDABCDABCDABCD'

Both methods work, but my instinct is that method 1 is better as only the input value is worked on by the unhex function, whereas in method 2 every value in the id column of the table will be put through the hex function.

Is this reasoning correct, or would MySQL optimise the query to prevent this? Are there any other reasons for choosing one method over the other?

Answer 1

When you use any functions on columns, using indexes becomes hard or impossible. I'm not sure if MySQL supports indexes with functions, but it's still more complicated than using just the column.

Also as you say the function has to be run for each row, whereas in the other only once for input data.

For these reasons do use the form with unhex.

Answer 2

If there is an index on the id column the first method is much faster. If there is no index, the first method is still more efficient.

With the first method, the UNHEX function can be called just once, and if there is a index, it is used. The second method call the function for each row of the table and does not use the index.