Bug #67637 EXPLAIN UPDATE output doesn't match query execution
Submitted: 19 Nov 2012 16:44 Modified: 22 Jan 2013 13:50
Reporter: Sergey Petrunya Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Optimizer Severity:S3 (Non-critical)
Version:5.6.7, 5.6.8 OS:Any
Assigned to: CPU Architecture:Any

[19 Nov 2012 16:44] Sergey Petrunya
Description:
EXPLAIN UPDATE output doesn't match how the query executes. 

Run this example (can be used as .test file):
CREATE TABLE `ten` (
  `a` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
INSERT INTO `ten` VALUES (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);

CREATE TABLE `t10` (
  `a` int(11) DEFAULT NULL,
  `b` int(11) DEFAULT NULL,
  KEY `a` (`a`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
insert into t10 
select 
 A.a+10*B.a+100*C.a + 1000*D.a, 
 A.a+10*B.a+100*C.a + 1000*D.a 
from 
  ten A, ten B, ten C, ten D, ten E;

explain update t10 set b=10 order by a limit 2;
flush status;
update t10 set b=10 order by a limit 2;
show status like 'Handler%';

drop table ten, t10;
EOF

And you will get:
explain update t10 set b=10 order by a limit 2;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t10	ALL	a	a	5	NULL	2	Using temporary
flush status;
update t10 set b=10 order by a limit 2;
show status like 'Handler%';
Variable_name	Value
Handler_commit	1
Handler_delete	0
Handler_discover	0
Handler_external_lock	2
Handler_mrr_init	0
Handler_prepare	0
Handler_read_first	1
Handler_read_key	3
Handler_read_last	0
Handler_read_next	1
Handler_read_prev	0
Handler_read_rnd	2
Handler_read_rnd_next	0
Handler_rollback	0
Handler_savepoint	0
Handler_savepoint_rollback	0
Handler_update	2
Handler_write	0
drop table ten, t10;

The table has 10K rows. EXPLAIN wants to run a full scan. Handler counters show it only reads 3 records, which means it will use an index in reality.  #rows is also from the index.

How to repeat:
see above.
[19 Nov 2012 16:53] Sergey Petrunya
Correction: the table has not 10K rows, but 100k. Everything else holds.
[22 Jan 2013 13:50] Paul Dubois
Noted in 5.6.10, 5.7.1 changelogs.

For single-table DELETE or UPDATE statements, EXPLAIN displayed a
type value of ALL (full-table scan access method) even if the
optimizer chose to scan the table by an index access method. Now the
type value is displayed as index.