Description:
Starting MySQL 4.1 one can no more get index used both for case insensitive comparisons
and case sensitive comparisons.
CREATE TABLE `my` (
`c` char(20) default NULL,
KEY `c` (`c`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1
MySQL 4.0
mysql> explain select * from my where c= binary "aaa";
+-------+------+---------------+------+---------+-------+------+--------------------------+
| table | type | possible_keys | key | key_len | ref | rows | Extra
|
+-------+------+---------------+------+---------+-------+------+--------------------------+
| my | ref | c | c | 21 | const | 2 | Using where; Using index
|
+-------+------+---------------+------+---------+-------+------+--------------------------+
1 row in set (0.00 sec)
MySQL 4.1
mysql> explain select * from my where c=binary "aaa";
+----+-------------+-------+-------+---------------+------+---------+------+------+--------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra
|
+----+-------------+-------+-------+---------------+------+---------+------+------+--------------------------+
| 1 | SIMPLE | my | index | NULL | c | 21 | NULL | 9 | Using
where; Using index |
+----+-------------+-------+-------+---------------+------+---------+------+------+--------------------------+
1 row in set (0.00 sec)
As you can see in last case full index is scanned.
One Can workaround it this way:
mysql> explain select * from my where c="aaa" and c=binary "aaa";
+----+-------------+-------+------+---------------+------+---------+-------+------+--------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra
|
+----+-------------+-------+------+---------------+------+---------+-------+------+--------------------------+
| 1 | SIMPLE | my | ref | c | c | 21 | const | 2 | Using
where; Using index |
+----+-------------+-------+------+---------------+------+---------+-------+------+--------------------------+
1 row in set (0.01 sec)
However why does not MySQL do it by itself ?
Someone could tell "binary" has different meaning in MySQL 4.1, so I tried using binary
collation instead:
mysql> explain select * from my where c="aaa" collate latin1_bin;
+----+-------------+-------+-------+---------------+------+---------+------+------+--------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra
|
+----+-------------+-------+-------+---------------+------+---------+------+------+--------------------------+
| 1 | SIMPLE | my | index | c | c | 21 | NULL | 9 | Using
where; Using index |
+----+-------------+-------+-------+---------------+------+---------+------+------+--------------------------+
1 row in set (0.00 sec)
So it does not work even right way :)
How to repeat:
CREATE TABLE my (
c char(20) default NULL,
KEY c (c)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
--
-- Dumping data for table `my`
--
INSERT INTO my VALUES
('111'),('222'),('222'),('222'),('222'),('444'),('aaa'),('AAA'),('bbb');
explain select * from my where c="aaa" collate latin1_bin;
explain select * from my where c=binary "aaa";
Description: Starting MySQL 4.1 one can no more get index used both for case insensitive comparisons and case sensitive comparisons. CREATE TABLE `my` ( `c` char(20) default NULL, KEY `c` (`c`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 MySQL 4.0 mysql> explain select * from my where c= binary "aaa"; +-------+------+---------------+------+---------+-------+------+--------------------------+ | table | type | possible_keys | key | key_len | ref | rows | Extra | +-------+------+---------------+------+---------+-------+------+--------------------------+ | my | ref | c | c | 21 | const | 2 | Using where; Using index | +-------+------+---------------+------+---------+-------+------+--------------------------+ 1 row in set (0.00 sec) MySQL 4.1 mysql> explain select * from my where c=binary "aaa"; +----+-------------+-------+-------+---------------+------+---------+------+------+--------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+-------+---------------+------+---------+------+------+--------------------------+ | 1 | SIMPLE | my | index | NULL | c | 21 | NULL | 9 | Using where; Using index | +----+-------------+-------+-------+---------------+------+---------+------+------+--------------------------+ 1 row in set (0.00 sec) As you can see in last case full index is scanned. One Can workaround it this way: mysql> explain select * from my where c="aaa" and c=binary "aaa"; +----+-------------+-------+------+---------------+------+---------+-------+------+--------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+------+---------------+------+---------+-------+------+--------------------------+ | 1 | SIMPLE | my | ref | c | c | 21 | const | 2 | Using where; Using index | +----+-------------+-------+------+---------------+------+---------+-------+------+--------------------------+ 1 row in set (0.01 sec) However why does not MySQL do it by itself ? Someone could tell "binary" has different meaning in MySQL 4.1, so I tried using binary collation instead: mysql> explain select * from my where c="aaa" collate latin1_bin; +----+-------------+-------+-------+---------------+------+---------+------+------+--------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+-------+---------------+------+---------+------+------+--------------------------+ | 1 | SIMPLE | my | index | c | c | 21 | NULL | 9 | Using where; Using index | +----+-------------+-------+-------+---------------+------+---------+------+------+--------------------------+ 1 row in set (0.00 sec) So it does not work even right way :) How to repeat: CREATE TABLE my ( c char(20) default NULL, KEY c (c) ) ENGINE=MyISAM DEFAULT CHARSET=latin1; -- -- Dumping data for table `my` -- INSERT INTO my VALUES ('111'),('222'),('222'),('222'),('222'),('444'),('aaa'),('AAA'),('bbb'); explain select * from my where c="aaa" collate latin1_bin; explain select * from my where c=binary "aaa";