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?
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?