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.
Best Answer
Index is case insensitive in your case, but optimizer is clever enough to use it efectively anyway.
In first explain, mysql uses ref access method - that means that it is able to directly fetch rows with given value.
In second explain, it says "using where" and uses range access - it takes the string, fetches all values from index searching for that string case insensitive, and for each value it checks the (case senstive/BINARY) WHERE condition to decide if it should be really returned - efectiveness of the access is the same, only drawback is that the case sensitive comparison has to be done afterwards, which is only small penalty compared to table scan which would have to compare every row the same way.