Description:
EXPLAIN will show "Backward index scan" in the Extra column when the optimizer plans to scan the index in reverse direction.
However, this works only for EXPLAIN SELECT. "Backward index scan" is not shown for EXPLAIN UPDATE or EXPLAIN DELETE statements.
It's the same with EXPLAIN FORMAT=JSON.
How to repeat:
create table one_k(a int);
insert into one_k select A.a + B.a* 10 + C.a * 100 from ten A, ten B, ten C;
create table t1 (a int, b int, key(a));
insert into t1 select A.a+1000*B.a, 12345 from ten B, one_k A;
insert into t1 select 10000 + A.a+1000*B.a, 12345 from ten B, one_k A;
analyze table t1;
mysql> explain delete from t1 order by a desc limit 1;
+----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+-------+
| 1 | DELETE | t1 | NULL | index | NULL | a | 5 | NULL | 1 | 100.00 | NULL |
+----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+-------+
1 row in set, 1 warning (0,01 sec)
### No "backward index scan" shown.
### Now, let's try a SELECT:
mysql> explain select * from t1 order by a desc limit 1;
+----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+---------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+---------------------+
| 1 | SIMPLE | t1 | NULL | index | NULL | a | 5 | NULL | 1 | 100.00 | Backward index scan |
+----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+---------------------+
1 row in set, 1 warning (0,00 sec)
### Try an UPDATE and see that it doesn't show it, either:
mysql> explain update t1 set b=b+1 order by a desc limit 1;
+----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+-----------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+-----------------+
| 1 | UPDATE | t1 | NULL | index | NULL | a | 5 | NULL | 1 | 100.00 | Using temporary |
+----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+-----------------+
1 row in set, 1 warning (0,00 sec)
