|
Usually, WHERE condition gets optimized by removing reundant parts. But in some cases optimization does not work.
Good example: int_field=int_const1 AND int_field=int_const1
CREATE OR REPLACE TABLE t1 (a INT);
|
INSERT INTO t1 VALUES (1),(2),(3);
|
EXPLAIN EXTENDED SELECT * FROM t1 WHERE a=1 AND a=1;
|
SHOW WARNINGS;
|
+-------+------+--------------------------------------------------------------------------+
|
| Level | Code | Message |
|
+-------+------+--------------------------------------------------------------------------+
|
| Note | 1003 | select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` = 1 |
|
+-------+------+--------------------------------------------------------------------------+
|
The redundant part was correctly removed.
Good example: int_field=int_const1 AND int_field=int_const2
CREATE OR REPLACE TABLE t1 (a INT);
|
INSERT INTO t1 VALUES (1),(2),(3);
|
EXPLAIN EXTENDED SELECT * FROM t1 WHERE a=1 AND a=2;
|
SHOW WARNINGS;
|
+-------+------+--------------------------------------------------------+
|
| Level | Code | Message |
|
+-------+------+--------------------------------------------------------+
|
| Note | 1003 | select `test`.`t1`.`a` AS `a` from `test`.`t1` where 0 |
|
+-------+------+--------------------------------------------------------+
|
The condition was correctly detected as impossible.
Good example: decimal_field=decimal_const1 AND decimal_field=int_const1
CREATE OR REPLACE TABLE t1 (a DECIMAL(10,3));
|
INSERT INTO t1 VALUES (1),(2),(3);
|
EXPLAIN EXTENDED SELECT * FROM t1 WHERE a=1.0 AND a=1;
|
SHOW WARNINGS;
|
+-------+------+----------------------------------------------------------------------------+
|
| Level | Code | Message |
|
+-------+------+----------------------------------------------------------------------------+
|
| Note | 1003 | select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` = 1.0 |
|
+-------+------+----------------------------------------------------------------------------+
|
The redundant part was correctly removed.
Good example: decimal_field=decimal_const1 AND decimal_field=int_const2
CREATE OR REPLACE TABLE t1 (a DECIMAL(10,3));
|
INSERT INTO t1 VALUES (1),(2),(3);
|
EXPLAIN EXTENDED SELECT * FROM t1 WHERE a=1.0 AND a=2;
|
SHOW WARNINGS;
|
+-------+------+--------------------------------------------------------+
|
| Level | Code | Message |
|
+-------+------+--------------------------------------------------------+
|
| Note | 1003 | select `test`.`t1`.`a` AS `a` from `test`.`t1` where 0 |
|
+-------+------+--------------------------------------------------------+
|
The condition was correctly detected as impossible.
Bad example: int_field=int_const1 AND int_field=decimal_const1
CREATE OR REPLACE TABLE t1 (a INT);
|
INSERT INTO t1 VALUES (1),(2),(3);
|
EXPLAIN EXTENDED SELECT * FROM t1 WHERE a=1 AND a=1.0;
|
SHOW WARNINGS;
|
+-------+------+----------------------------------------------------------------------------------------------------+
|
| Level | Code | Message |
|
+-------+------+----------------------------------------------------------------------------------------------------+
|
| Note | 1003 | select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` = 1 and `test`.`t1`.`a` = 1.0 |
|
+-------+------+----------------------------------------------------------------------------------------------------+
|
The redundant part was not removed.
Bad example: int_field=int_const1 AND int_field=decimal_const2
CREATE OR REPLACE TABLE t1 (a INT);
|
INSERT INTO t1 VALUES (1),(2),(3);
|
EXPLAIN EXTENDED SELECT * FROM t1 WHERE a=1 AND a=2.0;
|
SHOW WARNINGS;
|
+-------+------+----------------------------------------------------------------------------------------------------+
|
| Level | Code | Message |
|
+-------+------+----------------------------------------------------------------------------------------------------+
|
| Note | 1003 | select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` = 1 and `test`.`t1`.`a` = 2.0 |
|
+-------+------+----------------------------------------------------------------------------------------------------+
|
The condition was not detected as impossible.
CREATE OR REPLACE TABLE t1 (a INT);
|
INSERT INTO t1 VALUES (1),(2),(3);
|
EXPLAIN EXTENDED SELECT * FROM t1 WHERE a=1 AND a=1.1;
|
SHOW WARNINGS;
|
+-------+------+----------------------------------------------------------------------------------------------------+
|
| Level | Code | Message |
|
+-------+------+----------------------------------------------------------------------------------------------------+
|
| Note | 1003 | select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` = 1 and `test`.`t1`.`a` = 1.1 |
|
+-------+------+----------------------------------------------------------------------------------------------------+
|
The condition was not detected as impossible.
|