Bug #113405 'covering index' is not shown entirely in EXPLAIN FORMAT=TREE.

• Submitted: 13 Dec 2023 11:04
• Modified: 14 Dec 2023 4:50
• Reporter: tianfeng li (OCA)
• Status: Closed
• Category: MySQL Server: Optimizer
• Severity: S3 (Non-critical)
• Version: 8.0.35
• OS: Any
• CPU Architecture: Any
• Tags: Contribution, Explain plan

[13 Dec 2023 11:04] tianfeng li

Description:
EXPLAIN FORMAT=TREE sometimes didn't show 'Covering index' while 'EXPLAIN ANALYZE' did.

How to repeat:
CREATE TABLE `t2` (
  `a` varchar(255) NOT NULL,
  PRIMARY KEY (`a`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3;

explain format=tree select * from t2;
+--------------------------------------------------------+
| EXPLAIN                                                |
+--------------------------------------------------------+
| -> Index scan on t2 using PRIMARY  (cost=0.35 rows=1)
 |
+--------------------------------------------------------+

explain analyze  select * from t2;
+-----------------------------------------------------------------------------------------------------------+
| EXPLAIN                                                                                                   |
+-----------------------------------------------------------------------------------------------------------+
| -> Covering index scan on t2 using PRIMARY  (cost=0.35 rows=1) (actual time=0.198..0.198 rows=0 loops=1)
 |
+-----------------------------------------------------------------------------------------------------------+

Suggested fix:

In explain_access_path.cc, 'table->keyread' was tested to show whether the table use covering index, however, in many cases, 'table->set_keyread(true);' was done at execution stage where explain statement never arrived, such as IndexScanIterator<Reverse>::Init(), IndexSkipScanIterator::Init(), GroupIndexSkipScanIterator::Init(), IndexMergeIterator::Init(), IndexRangeScanIterator::Init().

The fix would be applied later.

[13 Dec 2023 11:05] tianfeng li

'covering index' is not shown entirely in EXPLAIN FORMAT=TREE.

(*) I confirm the code being submitted is offered under the terms of the OCA, and that I am authorized to contribute it.

Contribution: 0001-covering-index-is-not-shown-entirely-in-EXPLAIN-FORM.patch (application/octet-stream, text), 193.88 KiB.

[13 Dec 2023 11:27] MySQL Verification Team

Hello tianfeng li,

Thank you for the report and contribution.

regards,
Umesh

[14 Dec 2023 4:50] MySQL Verification Team

Thank you, tianfeng li for your contribution.
Internally confirmed that this is already fixed and pushed in MySQL 8.1.0 as part of internally reported Bug 34527697 - EXPLAIN and EXPLAIN ANALYZE disagree on covering index scans. 

Also, https://github.com/mysql/mysql-server/commit/15d92e7a11ab4b409f2e972fd1514760181d8a68
https://github.com/mysql/mysql-server/commit/217f90ad4877ad2fdab91e73afbbdbcba3aff0c4