| Bug #117666 | EXPLAIN FORMAT=TREE does not say the index is covering, unlike plain EXPLAIN | ||
|---|---|---|---|
| Submitted: | 11 Mar 10:59 | Modified: | 25 Mar 22:52 |
| Reporter: | Guilhem Bichot | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | MySQL Server: Optimizer | Severity: | S2 (Serious) |
| Version: | 8.0.41 | OS: | Any |
| Assigned to: | CPU Architecture: | Any | |
[11 Mar 11:08]
MySQL Verification Team
Hello Guilhem, Thank you for the report and test case. regards, Umesh
[13 Mar 16:57]
Jean-François Gagné
I think this should be Severity S2, because as explained by Norvald at FOSDEM [1], EXPLAIN FORMAT=TRADITIONAL should be considered legacy, and EXPLAIN FORMAT=TREE is the future. There might even be an argument to be made that any feature present in traditional and not present in tree is critical, as it will mean a lost feature when the old plan format will be removed. [1]: https://fosdem.org/2025/schedule/event/fosdem-2025-5466-the-past-present-and-future-of-exp...
[14 Mar 6:17]
MySQL Verification Team
Thank you, Jean-François for your feedback. regards, Umesh
[17 Mar 8:56]
Knut Anders Hatlen
Posted by developer: This bug was fixed in MySQL 8.1.0 as part of Bug#34527697.
[17 Mar 13:37]
Jean-François Gagné
> This bug was fixed in MySQL 8.1.0 as part of Bug#34527697. I do not fine any reference to Bug#34527697 in the 8.1.0 release notes [1], is this the right bug number ? [1]: https://dev.mysql.com/doc/relnotes/mysql/8.1/en/news-8-1-0.html
[25 Mar 22:54]
Jon Stephens
Bonjour,
I can verify that the bug was fixed as described here:
EXPLAIN and EXPLAIN ANALYZE did not produce the same output for
covering index scans.
There is a changelog entry for it, but I think it got in too late and we're no longer updating that changelog.
Thanks!
Description: Verified with 8.0.41 from github. mysql> explain format=tree select a from t3; +--------------------------------------------------+ | EXPLAIN | +--------------------------------------------------+ | -> Index scan on t3 using a (cost=0.45 rows=2) | +--------------------------------------------------+ 1 row in set (0.00 sec) mysql> explain select a from t3; +----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+-------------+ | 1 | SIMPLE | t3 | NULL | index | NULL | a | 5 | NULL | 2 | 100.00 | Using index | +----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+-------------+ 1 row in set, 1 warning (0.00 sec) See how the second EXPLAIN says "using index", which means it's covering. But the first one says "index scan" instead of "covering index scan". EXPLAIN FORMAT=TREE has the capability of printing "covering", here: static bool SetIndexInfoInObject( string *str, const char *json_index_access_type, const char *prefix, TABLE *table, const KEY *key, const char *index_access_type, const string lookup_condition, const string *ranges_text, std::unique_ptr<Json_array> range_arr, bool reverse, Item *pushed_idx_cond, Json_object *obj) { string idx_cond_str = pushed_idx_cond ? ItemToString(pushed_idx_cond) : ""; string covering_index = string(table->key_read ? "Covering index " : "Index "); <<<<< HERE But as you can see it only tests table->key_read. Non-tree EXPLAIN has a more complex test: bool Explain_join::explain_extra() { ... if (((tab->type() == JT_INDEX_SCAN || tab->type() == JT_CONST) && table->covering_keys.is_set(tab->index())) || (range_scan_type == AccessPath::ROWID_INTERSECTION && range_scan_path->rowid_intersection().is_covering) || ... table->key_read || tab->keyread_optim()) { ... if (push_extra(ET_USING_INDEX)) return true; } } In my case, key_read is false, but covering_keys.is_set is true. The same thing happens with a myisam table (you can try 'alter table t3 engine=myisam' and see). For a user, it matters: if mysql realizes the key is covering, it can avoid going to the clustered index (of innodb) or the data file (of myisam). Here mysql has realized it's covering, but does not say it to the user: EXPLAIN FORMAT=TREE makes the user think mysql is doing useless extra reads. It is misleading for users, developers, DBAs. How to repeat: create table t3 (a int, key(a)) engine=innodb; insert into t3 values (1),(2); explain format=tree select a from t3; explain select a from t3; Suggested fix: make both branches of EXPLAIN use the same complex test? or make sure table->key_read is set to true in all cases of index-only read?