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";