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:
Category:MySQL Server: Optimizer Severity:S3 (Non-critical)
Version:5.6 OS:Any
Assigned to: CPU Architecture:Any
Tags: explain, range, using where
Triage: Needs Triage: D4 (Minor)

[7 Feb 2013 5:40] xiaobin lin
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)
04.mysql> insert into tb values(1,1);
05.Query OK, 1 row affected (0.00 sec)
07.mysql> insert into tb values(2,2);
08.Query OK, 1 row affected (0.00 sec)
10.mysql> explain select * from tb where id=1;
12.| id | select_type | table | type  | possible_keys | key     | key_len | ref   | rows | Extra |
14.|  1 | SIMPLE      | tb    | const | PRIMARY       | PRIMARY | 4       | const |    1 | NULL  |
16.1 row in set (0.00 sec)
18.mysql> explain update tb   tb set c=2 where id=1;             
20.| id | select_type | table | type  | possible_keys | key     | key_len | ref  | rows | Extra       |
22.|  1 | SIMPLE      | tb    | range | PRIMARY       | PRIMARY | 4       | NULL |    1 | Using where |

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.
[7 Feb 2013 5:41] xiaobin lin
Based on 5.6.10

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

[7 Feb 2013 11:52] Miguel Solorzano
Thank you for the bug report and contribution.
[8 Apr 2013 8:12] Jørgen Løland
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.
[30 May 2013 2:07] Paul Dubois
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.