Bug #118174 Skip Scan doesn't support non-covering key scan
Submitted: 13 May 6:43 Modified: 14 May 7:16
Reporter: Liangyong Yu Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Optimizer Severity:S3 (Non-critical)
Version:8.0 OS:Any
Assigned to: CPU Architecture:Any

[13 May 6:43] Liangyong Yu
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;
[14 May 7:16] MySQL Verification Team
Hello Liangyong Yu,

Thank you for the report and feedback.

regards,
Umesh