Description:
Hi, MySQL developers. I wanna recommend an optimization about ABS().
When querying with ABS(t0.c0) < 10, MySQL performs a full table scan even though the equivalent condition t0.c0 > -10 AND t0.c0 < 10 can be executed as a range scan. The optimizer does not transform the ABS() predicate into a range condition, preventing efficient index usage.
How to repeat:
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(c0) VALUES (i);
SET i = i + 1;
SET batch_size = batch_size - 1;
END WHILE;
COMMIT;
SET batch_size = 1000;
END WHILE;
END //
DELIMITER ;
CREATE TABLE t0(c0 INT PRIMARY KEY);
CALL batch_insert_numbers();
-- negative case
EXPLAIN SELECT c0 FROM t0 WHERE ABS(t0.c0)<10 \G
*************************** 1. row ***************************
EXPLAIN: -> Filter: (abs(t0.c0) < 10) (cost=100277 rows=999000)
-> Covering index scan on t0 using PRIMARY (cost=100277 rows=999000)
-- positive case
EXPLAIN SELECT c0 FROM t0 WHERE t0.c0 > -10 AND t0.c0 < 10 \G
*************************** 1. row ***************************
EXPLAIN: -> Filter: ((t0.c0 > <cache>(-(10))) and (t0.c0 < 10)) (cost=2.06 rows=9)
-> Covering index range scan on t0 using PRIMARY over (-10 < c0 < 10) (cost=2.06 rows=9)
Suggested fix:
Add a predicate transformation rule in the logical optimization phase to convert: Where N is a non-negative constant (literal or foldable expression), we can transform ABS(col) < N into col > -N AND col < N.
© 2026, Oracle Corporation and/or its affiliates
Description: Hi, MySQL developers. I wanna recommend an optimization about ABS(). When querying with ABS(t0.c0) < 10, MySQL performs a full table scan even though the equivalent condition t0.c0 > -10 AND t0.c0 < 10 can be executed as a range scan. The optimizer does not transform the ABS() predicate into a range condition, preventing efficient index usage. How to repeat: 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(c0) VALUES (i); SET i = i + 1; SET batch_size = batch_size - 1; END WHILE; COMMIT; SET batch_size = 1000; END WHILE; END // DELIMITER ; CREATE TABLE t0(c0 INT PRIMARY KEY); CALL batch_insert_numbers(); -- negative case EXPLAIN SELECT c0 FROM t0 WHERE ABS(t0.c0)<10 \G *************************** 1. row *************************** EXPLAIN: -> Filter: (abs(t0.c0) < 10) (cost=100277 rows=999000) -> Covering index scan on t0 using PRIMARY (cost=100277 rows=999000) -- positive case EXPLAIN SELECT c0 FROM t0 WHERE t0.c0 > -10 AND t0.c0 < 10 \G *************************** 1. row *************************** EXPLAIN: -> Filter: ((t0.c0 > <cache>(-(10))) and (t0.c0 < 10)) (cost=2.06 rows=9) -> Covering index range scan on t0 using PRIMARY over (-10 < c0 < 10) (cost=2.06 rows=9) Suggested fix: Add a predicate transformation rule in the logical optimization phase to convert: Where N is a non-negative constant (literal or foldable expression), we can transform ABS(col) < N into col > -N AND col < N.