I noticed that Ruby on Rails (ActiveRecord) was using the BINARY
operator for a case sensitive comparison. Yet, when I look at the EXPLAIN
for this, MySQL can still use the appropriate index for a case insensitive column. How is that possible? Are all text columns indexed case sensitivly?
Without BINARY
operator:
select_type: SIMPLE
table: my_table
type: const
possible_keys: my_table_unq,my_table_idx
key: my_table_unq
key_len: 771
ref: const,const
rows: 1
Extra: Using index
With BINARY
operator:
select_type: SIMPLE
table: my_table
type: range
possible_keys: my_table_unq,my_table_idx
key: my_table_unq
key_len: 771
ref: NULL
rows: 1
Extra: Using where; Using index
Looks like the later is using a range.