1

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.

6
  • Show us the explain plan. It would worth I thin if you also posted the explain for the same query (with the BINARY operator removed). Commented Jun 23, 2015 at 17:36
  • Well, when index is case insensitive, you can locate all rows (with all cases) for the given string by it and then only exclude some by case sensitive check. You will load a bit more items but generally not many times more.
    – jkavalik
    Commented Jun 23, 2015 at 17:44
  • @user1786423 Yes, I was wondering more why it was not a full table scan Commented Jun 23, 2015 at 18:07
  • 1
    @CarsonReinke as your explain shows it went from ref to range (range of all case versions probably) and "using where" to check (ref is for direct matches, so when it will later compare them, it just has to do range) - but optimizer is just clever enough to know this case.
    – jkavalik
    Commented Jun 23, 2015 at 18:12
  • @user1786423 You should post that as an answer Commented Jun 23, 2015 at 19:59

2 Answers 2

2

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.

2

Note the link you gave on the BINARY operator where is it says the following

Posted by Yann Neuhaus on July 20 2005 12:34pm

Per default the search operation in not case sensitive, example, looking for 'HYPE BEAU' returns 'Hype Beau' in the table product :

mysql> select prod_name, prod_id from products where prod_name = 'HYPE BEAU';

+-----------+---------+
| prod_name | prod_id |
+-----------+---------+
| Hype Beau |    9600 |
| Hype Beau |   25860 |
| Hype Beau |   42120 |
| Hype Beau |   46185 |
+-----------+---------+

4 rows in set (0.01 sec)

This is because the default collation is case insentitive. With the binary clause the binary collation is used and the search becomes case sensitive :

If the character set's collation is case insensitive, then using the BINARY operator is not necessary. It would only appear to work fine in that instance.

For more clarification on collations being case insensitive, please read the MySQL Documentation section "The _bin and binary Collations"

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.