sql - MySQL byte-for-byte comparison, which is faster? binary vs bin_collate

Question

Assume we have a table that looks like this:

create table t1(c1 varchar(x)collate utf8mb4_general_ci, index(c1))

To do byte-sensitive comparisons, we basically have two ways (assume that all relevant strings do not have trailing spaces, i.e. they are all padspace-compliant):

select*from t1 where c1 ='test'collate utf8mb4_bin

select*from t1 where c1 = binary'test'

Which should be preferred when performance is of concern?

When using an index of non-binary character collation, is it faster to compare with binary string or binary collation?

(Adding a new column to the table just to store the binary equivalent of c1 is a big hit on storage and not possible.)

(P.S. Would appreciate an answer that compares both hash and btree comparisons, although I'm primarily interested in btree comparison.)

Answer 1

As you have index in the table,for binary match use binary for constant,not to the column. This will be faster than both of your options.

select * from t1 where c1 = binary 'test'

Answer to you question is option 1 will be faster where you are doing

WHERE c1 collate utf8mb4_bin='test'