Description:
Problem 1:
Push the processing of the offset down to InnoDB
Why:
By Skipping rows down in the storage engine, we do not have to return to the sql layer rows that are not going to be returned anyways
It is especially relevant for non-covering index because then the rows are skipped without ever having to be read from the base table
Problem 2:
Some conditions in index range scans are redundant in the sense that the storage engine already enforces that conditions but they are checked again in the sql layer
Fix: Assess if the condition is redundant, and remove it. Side benefit: because pushing down the offset is only going to be possible when the sql does not have to carry out any checks on the rows that are returned, this would broaden the scope of the queries for which the offset can be pushed down.
How to repeat:
Problem 1:
CREATE TABLE t1 (a INT , b INT , INDEX(b));
INSERT INTO t1 VALUES (1, 2), (2, 3), (3, 3), (4, 3);
explain format=tree SELECT * FROM t1 LIMIT 1 OFFSET 100;
In this case, we could simply skip all the rows in the storage engine without ever having to return them to the sql layer
Problem 2:
mysql> CREATE TABLE t3 (a INT , b INT , INDEX(a,b));
RT INTO t3 VALUES (1, 2),(2, 3),(3, 3),(4, 3);Query OK, 0 rows affected (0.04 sec)
mysql> INSERT INTO t3 VALUES (1, 2),(2, 3),(3, 3),(4, 3);
Query OK, 4 rows affected (0.02 sec)
Records: 4 Duplicates: 0 Warnings: 0
mysql> explain format=tree SELECT a,b FROM t3 WHERE a = 2 AND b < 2;
+-----------------------------------------------------------------------------------------------------------------------+
| EXPLAIN |
+-----------------------------------------------------------------------------------------------------------------------+
| -> Filter: ((t3.a = 2) and (t3.b < 2)) (cost=0.46 rows=1)
-> Index range scan on t3 using a (cost=0.46 rows=1)
|
+-----------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
The Filter checks are redundant as they are already enforced by InnoDB, these checks could be removed
Suggested fix:
I am going to propose a feature "solving" this problem.
Description: Problem 1: Push the processing of the offset down to InnoDB Why: By Skipping rows down in the storage engine, we do not have to return to the sql layer rows that are not going to be returned anyways It is especially relevant for non-covering index because then the rows are skipped without ever having to be read from the base table Problem 2: Some conditions in index range scans are redundant in the sense that the storage engine already enforces that conditions but they are checked again in the sql layer Fix: Assess if the condition is redundant, and remove it. Side benefit: because pushing down the offset is only going to be possible when the sql does not have to carry out any checks on the rows that are returned, this would broaden the scope of the queries for which the offset can be pushed down. How to repeat: Problem 1: CREATE TABLE t1 (a INT , b INT , INDEX(b)); INSERT INTO t1 VALUES (1, 2), (2, 3), (3, 3), (4, 3); explain format=tree SELECT * FROM t1 LIMIT 1 OFFSET 100; In this case, we could simply skip all the rows in the storage engine without ever having to return them to the sql layer Problem 2: mysql> CREATE TABLE t3 (a INT , b INT , INDEX(a,b)); RT INTO t3 VALUES (1, 2),(2, 3),(3, 3),(4, 3);Query OK, 0 rows affected (0.04 sec) mysql> INSERT INTO t3 VALUES (1, 2),(2, 3),(3, 3),(4, 3); Query OK, 4 rows affected (0.02 sec) Records: 4 Duplicates: 0 Warnings: 0 mysql> explain format=tree SELECT a,b FROM t3 WHERE a = 2 AND b < 2; +-----------------------------------------------------------------------------------------------------------------------+ | EXPLAIN | +-----------------------------------------------------------------------------------------------------------------------+ | -> Filter: ((t3.a = 2) and (t3.b < 2)) (cost=0.46 rows=1) -> Index range scan on t3 using a (cost=0.46 rows=1) | +-----------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec) The Filter checks are redundant as they are already enforced by InnoDB, these checks could be removed Suggested fix: I am going to propose a feature "solving" this problem.