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.

Portions of this website are copyright © 2001, 2002 The PHP Group

Page generated in 0.052 sec. using MySQL 8.4.7-u6-cloud

Timestamp references displayed by the system are UTC.
Content reproduced on this site is the property of the respective copyright holders.
It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.