rails/rails — pull request #13040 by kamipo (2013-11-25 20:50:38+00:00)
Title: Only use BINARY for mysql case sensitive uniqueness check when column has a case insensitive collation.

I think that should use BINARY for mysql case sensitive uniqueness check when column has a case insensitive collation.

If use BINARY in MySQL, index is not used efficiently.

```
CREATE TABLE `users` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`username` varchar(255) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,
`created_at` datetime DEFAULT NULL,
`updated_at` datetime DEFAULT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `idx_username` (`username`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
```

Do not use BINARY:

```
> EXPLAIN SELECT * FROM users WHERE username = 'kamipo@facebook.com';
+----+-------------+-------+-------+---------------+--------------+---------+-------+------+-------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+-------+---------------+--------------+---------+-------+------+-------+
| 1 | SIMPLE | users | const | idx_username | idx_username | 767 | const | 1 | NULL |
+----+-------------+-------+-------+---------------+--------------+---------+-------+------+-------+
```

Use BINARY in MySQL 5.5 and earlier:

```
> EXPLAIN SELECT * FROM users WHERE username = BINARY 'kamipo@facebook.com';
+----+-------------+-------+-------+---------------+--------------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+-------+---------------+--------------+---------+------+------+-------------+
| 1 | SIMPLE | users | range | idx_username | idx_username | 767 | NULL | 1 | Using where |
+----+-------------+-------+-------+---------------+--------------+---------+------+------+-------------+
```

Use BINARY in MySQL 5.6(optimizer_switch='index_condition_pushdown=on') and later:

```
> SET optimizer_switch='index_condition_pushdown=on';
> EXPLAIN SELECT * FROM users WHERE username = BINARY 'kamipo@facebook.com';
+----+-------------+-------+-------+---------------+--------------+---------+------+------+-----------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+-------+---------------+--------------+---------+------+------+-----------------------+
| 1 | SIMPLE | users | range | idx_username | idx_username | 767 | NULL | 1 | Using index condition |
+----+-------------+-------+-------+---------------+--------------+---------+------+------+-----------------------+
```