Description:
Currently, the Skip Scan feature in MySQL only supports covering key scans.
For example:
CREATE TABLE t (a INT, b INT, c INT, PRIMARY KEY(a, b)) ENGINE=innodb;
* Query 1: SELECT a,b FROM t WHERE b > 1
In this case, the MySQL optimizer is able to choose a Skip Scan, since all the required fields are included in the index on (a, b) —— no need to perform a row lookup：
* Query 2: SELECT a,b,c FROM t WHERE b > 1
In this case, The optimizer does not choose Skip Scan. As noted in the optimizer_trace, the reason given is: "query_references_nonkey_column" —— meaning the query references a column (c) that is not present in the index.
I'm wondering why Skip Scan does not support row lookups via the index?
What are the limitations or potential issues that prevent this from being implemented? Thanks a lot!
How to repeat:
CREATE TABLE t (a INT, b INT, c INT, PRIMARY KEY(a, b)) ENGINE=innodb;
-- skip scan can be used
EXPLAIN SELECT /*+ SKIP_SCAN(t) */ a,b FROM t WHERE b > 1;
-- skip scan cannot be used
EXPLAIN SELECT /*+ SKIP_SCAN(t) */ a,b,c FROM t WHERE b > 1;
