|
When optimizing queries that have conditions on an indexed string column, we require that collation of the condition matches collation of the column.
In some cases (e.g. in range optimizer) we don't require strict collation match and additionally allow BINARY comparison and COLLATE xxx_bin comparision, even on a case insensitive column. These loose rules could be used in some more cases where they are not used now:
Example 1:
DROP TABLE IF EXISTS t1;
|
CREATE TABLE t1 (c1 VARCHAR(20) CHARACTER SET latin1, PRIMARY KEY(c1));
|
INSERT INTO t1 VALUES ('a'),('b'),('c'),('d');
|
EXPLAIN SELECT * FROM t1 WHERE c1=BINARY 'a';
|
returns:
+------+-------------+-------+-------+---------------+---------+---------+------+------+--------------------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
|
+------+-------------+-------+-------+---------------+---------+---------+------+------+--------------------------+
|
| 1 | SIMPLE | t1 | range | PRIMARY | PRIMARY | 22 | NULL | 1 | Using where; Using index |
|
+------+-------------+-------+-------+---------------+---------+---------+------+------+--------------------------+
|
It could use "const" access instead.
Example 2:
DROP TABLE IF EXISTS t1,t2;
|
CREATE TABLE t1 (c1 VARCHAR(10) CHARACTER SET latin1 COLLATE latin1_bin);
|
INSERT INTO t1 VALUES ('a');
|
CREATE TABLE t2 (c1 VARCHAR(10) CHARACTER SET latin1, PRIMARY KEY(c1));
|
INSERT INTO t2 VALUES ('a'),('b');
|
SELECT * FROM t1, t2 WHERE t1.c1=t2.c1;
|
EXPLAIN SELECT * FROM t1, t2 WHERE t1.c1=t2.c1;
|
returns
+------+-------------+-------+------+---------------+------+---------+------+------+------------------------------------------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
|
+------+-------------+-------+------+---------------+------+---------+------+------+------------------------------------------------+
|
| 1 | SIMPLE | t1 | ALL | NULL | NULL | NULL | NULL | 1 | |
|
| 1 | SIMPLE | t2 | ALL | PRIMARY | NULL | NULL | NULL | 2 | Range checked for each record (index map: 0x1) |
|
+------+-------------+-------+------+---------------+------+---------+------+------+------------------------------------------------+
|
It could use "eq_ref" instead of "ALL + Range changed for each record".
Example 3:
DROP TABLE IF EXISTS t1,t2;
|
CREATE TABLE t1 (c1 VARCHAR(10) CHARACTER SET latin1 COLLATE latin1_bin);
|
INSERT INTO t1 VALUES ('a');
|
CREATE TABLE t2 (c1 VARCHAR(10) CHARACTER SET latin1, PRIMARY KEY(c1));
|
INSERT INTO t2 VALUES ('a'),('b');
|
SELECT t1.* FROM t1 LEFT JOIN t2 USING (c1);
|
EXPLAIN SELECT t1.* FROM t1 LEFT JOIN t2 USING (c1);
|
returns
+------+-------------+-------+------+---------------+------+---------+------+------+------------------------------------------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
|
+------+-------------+-------+------+---------------+------+---------+------+------+------------------------------------------------+
|
| 1 | SIMPLE | t1 | ALL | NULL | NULL | NULL | NULL | 1 | |
|
| 1 | SIMPLE | t2 | ALL | PRIMARY | NULL | NULL | NULL | 2 | Range checked for each record (index map: 0x1) |
|
+------+-------------+-------+------+---------------+------+---------+------+------+------------------------------------------------+
|
It could safely eliminate table t2 from the query.
|