Description:
Hi, MySQL developers, I find a missed optimization in MySQL.
MySQL detects impossible WHERE conditions when a column is compared to a constant using both equality and an inequality (e.g., c0 > 0 AND c0 = 0), producing a zero-row result without table access. However, contradictory conditions that involve only inequalities (e.g., c0 > 0 AND c0 < 0, c0 >= 0 AND c0 < 0, c0 > 0 AND c0 <= 0) are not recognized as impossible. Instead, a full table scan with a filter is performed, which is wasteful for large tables.
How to repeat:
CREATE TABLE t0(c0 INT);
CALL batch_insert_numbers();
DELIMITER //
CREATE PROCEDURE batch_insert_numbers()
BEGIN
DECLARE i INT DEFAULT 1;
DECLARE batch_size INT DEFAULT 1000;
WHILE i <= 1000000 DO
START TRANSACTION;
WHILE i <= 1000000 AND batch_size > 0 DO
INSERT INTO t0 VALUES (i);
SET i = i + 1;
SET batch_size = batch_size - 1;
END WHILE;
COMMIT;
SET batch_size = 1000;
END WHILE;
END //
DELIMITER ;
-- positive cases
explain analyze select * from t0 where t0.c0 > 0 AND t0.c0 = 0 \G
explain analyze select * from t0 where t0.c0 < 0 AND t0.c0 = 0 \G
explain analyze select * from t0 where t0.c0 <> 0 AND t0.c0 = 0 \G
*************************** 1. row ***************************
EXPLAIN: -> Zero rows (Impossible WHERE) (cost=0..0 rows=0) (actual time=80e-6..80e-6 rows=0 loops=1)
1 row in set (0.001 sec)
-- negative cases
explain analyze select * from t0 where t0.c0 > 0 AND t0.c0 < 0 \G
*************************** 1. row ***************************
EXPLAIN: -> Filter: ((t0.c0 > 0) and (t0.c0 < 0)) (cost=100419 rows=110932) (actual time=293..293 rows=0 loops=1)
-> Table scan on t0 (cost=100419 rows=998585) (actual time=0.0279..247 rows=1e+6 loops=1)
1 row in set (0.294 sec)
explain analyze select * from t0 where t0.c0 >= 0 AND t0.c0 < 0 \G
*************************** 1. row ***************************
EXPLAIN: -> Filter: ((t0.c0 >= 0) and (t0.c0 < 0)) (cost=100419 rows=110932) (actual time=300..300 rows=0 loops=1)
-> Table scan on t0 (cost=100419 rows=998585) (actual time=0.113..254 rows=1e+6 loops=1)
1 row in set (0.301 sec)
mysql> explain analyze select * from t0 where t0.c0 > 0 AND t0.c0 <= 0 \G
*************************** 1. row ***************************
EXPLAIN: -> Filter: ((t0.c0 > 0) and (t0.c0 <= 0)) (cost=100419 rows=110932) (actual time=292..292 rows=0 loops=1)
-> Table scan on t0 (cost=100419 rows=998585) (actual time=0.0289..245 rows=1e+6 loops=1)
1 row in set (0.292 sec)
Suggested fix:
It could be fixed by rewriting col > a AND col < b to FALSE.
© 2026, Oracle Corporation and/or its affiliates
Description: Hi, MySQL developers, I find a missed optimization in MySQL. MySQL detects impossible WHERE conditions when a column is compared to a constant using both equality and an inequality (e.g., c0 > 0 AND c0 = 0), producing a zero-row result without table access. However, contradictory conditions that involve only inequalities (e.g., c0 > 0 AND c0 < 0, c0 >= 0 AND c0 < 0, c0 > 0 AND c0 <= 0) are not recognized as impossible. Instead, a full table scan with a filter is performed, which is wasteful for large tables. How to repeat: CREATE TABLE t0(c0 INT); CALL batch_insert_numbers(); DELIMITER // CREATE PROCEDURE batch_insert_numbers() BEGIN DECLARE i INT DEFAULT 1; DECLARE batch_size INT DEFAULT 1000; WHILE i <= 1000000 DO START TRANSACTION; WHILE i <= 1000000 AND batch_size > 0 DO INSERT INTO t0 VALUES (i); SET i = i + 1; SET batch_size = batch_size - 1; END WHILE; COMMIT; SET batch_size = 1000; END WHILE; END // DELIMITER ; -- positive cases explain analyze select * from t0 where t0.c0 > 0 AND t0.c0 = 0 \G explain analyze select * from t0 where t0.c0 < 0 AND t0.c0 = 0 \G explain analyze select * from t0 where t0.c0 <> 0 AND t0.c0 = 0 \G *************************** 1. row *************************** EXPLAIN: -> Zero rows (Impossible WHERE) (cost=0..0 rows=0) (actual time=80e-6..80e-6 rows=0 loops=1) 1 row in set (0.001 sec) -- negative cases explain analyze select * from t0 where t0.c0 > 0 AND t0.c0 < 0 \G *************************** 1. row *************************** EXPLAIN: -> Filter: ((t0.c0 > 0) and (t0.c0 < 0)) (cost=100419 rows=110932) (actual time=293..293 rows=0 loops=1) -> Table scan on t0 (cost=100419 rows=998585) (actual time=0.0279..247 rows=1e+6 loops=1) 1 row in set (0.294 sec) explain analyze select * from t0 where t0.c0 >= 0 AND t0.c0 < 0 \G *************************** 1. row *************************** EXPLAIN: -> Filter: ((t0.c0 >= 0) and (t0.c0 < 0)) (cost=100419 rows=110932) (actual time=300..300 rows=0 loops=1) -> Table scan on t0 (cost=100419 rows=998585) (actual time=0.113..254 rows=1e+6 loops=1) 1 row in set (0.301 sec) mysql> explain analyze select * from t0 where t0.c0 > 0 AND t0.c0 <= 0 \G *************************** 1. row *************************** EXPLAIN: -> Filter: ((t0.c0 > 0) and (t0.c0 <= 0)) (cost=100419 rows=110932) (actual time=292..292 rows=0 loops=1) -> Table scan on t0 (cost=100419 rows=998585) (actual time=0.0289..245 rows=1e+6 loops=1) 1 row in set (0.292 sec) Suggested fix: It could be fixed by rewriting col > a AND col < b to FALSE.