Bug #118177 Unnecessary filter operator in the execution plan
Submitted: 13 May 8:53 Modified: 22 May 6:27
Reporter: Bob Wong Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Optimizer Severity:S3 (Non-critical)
Version:8.0.35 OS:Any
Assigned to: CPU Architecture:Any

[13 May 8:53] Bob Wong
Description:
CREATE TABLE t1 (f1 INTEGER, f2 INTEGER, PRIMARY KEY (f1), KEY(f2, f1));
INSERT INTO t1 (
WITH RECURSIVE
a(i) AS (SELECT 0 UNION ALL SELECT i+1 FROM a WHERE i < 9 ),
b(i) AS (SELECT x.i + y.i * 10 + z.i * 100 FROM a x, a y, a z)
SELECT b.i, b.i %2 FROM b ORDER BY i);
ANALYZE TABLE t1;
Table   Op      Msg_type        Msg_text
test.t1 analyze status  OK
EXPLAIN SELECT * FROM t1 WHERE f2 = 1 AND f1 <= 100 ORDER BY f1 DESC LIMIT 1;
id      select_type     table   partitions      type    possible_keys   key     key_len ref     rows    filtered        Extra
1       SIMPLE  t1      NULL    range   PRIMARY,f2      f2      9       NULL    50      100.00  Using where; Backward index scan; Using index
Warnings:
Note    1003    /* select#1 */ select `test`.`t1`.`f1` AS `f1`,`test`.`t1`.`f2` AS `f2` from `test`.`t1` where ((`test`.`t1`.`f2` = 1) and (`test`.`t1`.`f1` <= 100)) order by `test`.`t1`.`f1` desc limit 1
EXPLAIN ANALYZE SELECT * FROM t1 WHERE f2 = 1 AND f1 <= 100 ORDER BY f1 DESC LIMIT 1;
EXPLAIN
-> Limit: 1 row(s)  (cost=10.3 rows=1) (actual time=0.331..0.332 rows=1 loops=1)
    -> Filter: ((t1.f2 = 1) and (t1.f1 <= 100))  (cost=10.3 rows=50) (actual time=0.33..0.33 rows=1 loops=1)
        -> Covering index range scan on t1 using f2 over (f2 = 1 AND f1 <= 100) (reverse)  (cost=10.3 rows=50) (actual time=0.306..0.306 rows=1 loops=1)

The index is (f2, f1), and the covering index range scan has already explicitly defined the scan range (f2=1 AND f1<=100), meaning there is theoretically no need for additional filtering at the Server layer.

How to repeat:
CREATE TABLE t1 (f1 INTEGER, f2 INTEGER, PRIMARY KEY (f1), KEY(f2, f1));
INSERT INTO t1 (
WITH RECURSIVE
a(i) AS (SELECT 0 UNION ALL SELECT i+1 FROM a WHERE i < 9 ),
b(i) AS (SELECT x.i + y.i * 10 + z.i * 100 FROM a x, a y, a z)
SELECT b.i, b.i %2 FROM b ORDER BY i);
ANALYZE TABLE t1;

EXPLAIN SELECT * FROM t1 WHERE f2 = 1 AND f1 <= 100 ORDER BY f1 DESC LIMIT 1;
i
EXPLAIN ANALYZE SELECT * FROM t1 WHERE f2 = 1 AND f1 <= 100 ORDER BY f1 DESC LIMIT 1;
[22 May 6:27] MySQL Verification Team
Hello Bob Wong,

Thank you for the report and test case.

regards,
Umesh