Bug #71027 Full-scan UPDATE is EXPLAINED confusingly
Submitted: 27 Nov 2013 19:50 Modified: 28 Nov 2013 13:26
Reporter: Baron Schwartz (Basic Quality Contributor) Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Optimizer Severity:S3 (Non-critical)
Version:5.6.11, 5.6.14 OS:Any
Assigned to: CPU Architecture:Any

[27 Nov 2013 19:50] Baron Schwartz 
Description:
EXPLAIN UPDATE on InnoDB shows a full-scan update as an "index" instead of "ALL" as I'm used to. Although it's technically correct, it's different from how it's historically been shown for SELECTs.

How to repeat:
This query has no usable index, on an InnoDB table:

EXPLAIN UPDATE ... WHERE col1 = 9 AND col2 = 'something'\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: foo
         type: index
possible_keys: NULL
          key: PRIMARY
      key_len: 55
          ref: NULL
         rows: 51
        Extra: Using where

If I do the old trick of rewriting it to a similar SELECT I see that:

*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: foo
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 51
        Extra: Using where

Suggested fix:
For type=index, index=PRIMARY, show ALL.
[28 Nov 2013 13:26] MySQL Verification Team 
Hello Baron,

Thank you for the bug report.
Verified as described.

Thanks,
Umesh