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;
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;