| Bug #120666 | Optimizer fails to convert ABS(column) < constant to range scan, resulting in full table scan | ||
|---|---|---|---|
| Submitted: | 12 Jun 15:58 | Modified: | 15 Jun 8:51 |
| Reporter: | jinhui lai | Email Updates: | |
| Status: | Verified | Impact on me: | |
| Category: | MySQL Server: Optimizer | Severity: | S5 (Performance) |
| Version: | 9.7.0 | OS: | Any |
| Assigned to: | CPU Architecture: | Any | |
[15 Jun 8:51]
Roy Lyseng
Thank you for the bug report. We consider this to be a reasonable request for optimization, although the impact is probably minor.
[15 Jun 23:40]
Jean-François Gagné
> We consider this to be a reasonable request for optimization, although the impact is probably minor. I would like to challenge the fact that this request for optimization is reasonable. It is well known that applying a function to a column prevents indexing (unless functional indexes are used). And because of that, the expectation that "ABS(t0.c0) < 10" uses an index should be considered misplaced. Considering that "ABS(t0.c0) < 10" can easily be rewritten to "t0.c0 > -10 AND t0.c0 < 10" by a smart developer, I do not think we should add complexity in MySQL to optimize this. I could change my mind if another SQL Engine is doing this, and if it is considered valuable by their community of user. I would also point-out that once we start optimizing ABS, we should also considering optimizing all of below, which I do not think are optimized. - t0.c0 + constant1 < constant2 - POW(t0.c0, constant1) < constant2 - SIGN(t0.c0) = -1 - SQRT(t0.c0) < constant - YEAR(t0.c0) = constant - probably others...
[16 Jun 6:23]
Roy Lyseng
Hi Jean-François, I agree this request has minor impact, and it is unlikely that we give it a high priority, unless there is a lot of responses in favor of it.
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.