How is MySQL using an index when doing a case sensitive comparison? 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.
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"