MySQL Bugs: #68299: unexcepted "Using where" in "explain update/delete"

Bug #68299	unexcepted "Using where" in "explain update/delete"
Submitted:	7 Feb 2013 5:40	Modified:	30 May 2013 2:07
Reporter:	xiaobin lin (OCA)	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Optimizer	Severity:	S3 (Non-critical)
Version:	5.6	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	explain, range, using where

Description:
Very glad to see the "explan insert/update/delete " in 5.6.
There seems to be a puzzle output when explain update/delete (As show in net segment).

How to repeat:
01.mysql> create table tb(id int primary key , c int);
02.Query OK, 0 rows affected (0.01 sec)
03. 
04.mysql> insert into tb values(1,1);
05.Query OK, 1 row affected (0.00 sec)
06. 
07.mysql> insert into tb values(2,2);
08.Query OK, 1 row affected (0.00 sec)
09. 
10.mysql> explain select * from tb where id=1;
11.+----+-------------+-------+-------+---------------+---------+---------+-------+------+-------+
12.| id | select_type | table | type  | possible_keys | key     | key_len | ref   | rows | Extra |
13.+----+-------------+-------+-------+---------------+---------+---------+-------+------+-------+
14.|  1 | SIMPLE      | tb    | const | PRIMARY       | PRIMARY | 4       | const |    1 | NULL  |
15.+----+-------------+-------+-------+---------------+---------+---------+-------+------+-------+
16.1 row in set (0.00 sec)
17. 
18.mysql> explain update tb   tb set c=2 where id=1;             
19.+----+-------------+-------+-------+---------------+---------+---------+------+------+-------------+
20.| id | select_type | table | type  | possible_keys | key     | key_len | ref  | rows | Extra       |
21.+----+-------------+-------+-------+---------------+---------+---------+------+------+-------------+
22.|  1 | SIMPLE      | tb    | range | PRIMARY       | PRIMARY | 4       | NULL |    1 | Using where |
23.+----+-------------+-------+

type=="range" and Extra="Using whee" is unexcepted here.
But what make things more puzzled is that when "explain update tb1, tb2 set .... where tb1.id=1 and tb2.id=1", the "type" is const and Extra is NULL, the later is excepted.

Suggested fix:
I look into it and find that the reason is that, differet from select and multipl-table-update, class Explain_table is used.(single-table UPDATE/DELETE are implemented without the regular JOIN tree) It seems that JT_CONST is not an option in Explain_table::explain_join_type().

I think there can be a check for the join_type, if the update is based on uniquekey (UNIQUE_RANGE) and queal (EQ_RANGE), the result returns "const" and NULL.

As the patch file bellow shows.

Based on 5.6.10

Contribution: 5.6.10.explain_update_using_where.diff (application/octet-stream, text), 1.40 KiB.

Thank you for the bug report and contribution.

Hi xiaobin lin,

Thank you for the bug report and the contribution. 

Your suggested modification would change the value of the 'type' column from "range" to "const" for UPDATE statements with equality predicates. Unfortunately, the problem is a bit more complicated than what EXPLAIN outputs because the statement currently does use range access (in contrast to the SELECT statement shown in this bug report). The output of EXPLAIN is therefore correct.

The proper way to fix this bug would be to make single-table UPDATEs (and DELETEs) go through the same optimizations that multi-table U/D and SELECTs. This is a bigger task that has become very visible due to the new EXPLAIN UPDATE feature in 5.6. It will be addressed sooner or later, but I can't give a time frame.

Your request for increased readability is still a good one though. We have decided to keep "type:range" for the statement in the bug report (since this is what actually happens) but instead change the value of the 'ref' column to "const" in the same way as is shown for queries with type [eq_]ref and const.

Noted in 5.6.12, 5.7.2 changelogs.

For DELETE and UPDATE statements, EXPLAIN displayed NULL in the ref
column for some cases where const is more appropriate.