| Bug #69887 | about explain of update | ||
|---|---|---|---|
| Submitted: | 1 Aug 2013 2:58 | Modified: | 5 Aug 2013 16:03 |
| Reporter: | kazushige uratani | Email Updates: | |
| Status: | Verified | Impact on me: | |
| Category: | MySQL Server: Optimizer | Severity: | S4 (Feature request) |
| Version: | 5.6.12, 5.6.13 | OS: | Linux |
| Assigned to: | CPU Architecture: | Any | |
[1 Aug 2013 6:11]
MySQL Verification Team
Hello! Thank you for the bug report. Verified as described on recent 5.6.13 version. Thanks, Umesh
[1 Aug 2013 6:16]
MySQL Verification Team
How to repeat:
use test;
DROP TABLE IF EXISTS `keyvalue`;
CREATE TABLE `keyvalue` (
`id` smallint unsigned NOT NULL DEFAULT '0',
`name` varchar(50) DEFAULT NULL,
`cnt` smallint unsigned NOT NULL DEFAULT '0',
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
set @id:=0;
insert into `keyvalue` values (@id:=@id+1,md5(rand()*1000000),@id:=@id+1),(@id:=@id+1,md5(rand()*1000000),@id:=@id+1),(@id:=@id+1,md5(rand()*1000000),@id:=@id+1),(@id:=@id+1,md5(rand()*1000000),@id:=@id+1);
insert into `keyvalue`(`id`,`name`,`cnt`)
select @id:=@id+1,md5(rand()*1000000),@id:=@id+1 from
`keyvalue` k1, `keyvalue` k2, `keyvalue` k3, `keyvalue` k4,`keyvalue` k5,`keyvalue` k6, `keyvalue` k7, `keyvalue` k8, `keyvalue` k9,
`keyvalue` k0,`keyvalue` ka, `keyvalue` kb, `keyvalue` kc, `keyvalue` kd limit 10000;
// EXPLAIN PLAN changes for SELECT and UPDATE
/// 5.6.13
mysql> select version();
+------------+
| version() |
+------------+
| 5.6.13-log |
+------------+
1 row in set (0.00 sec)
mysql> explain select * from keyvalue where id = 1\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: keyvalue
type: const <--------------------
possible_keys: PRIMARY
key: PRIMARY
key_len: 2
ref: const
rows: 1
Extra: NULL
1 row in set (0.02 sec)
mysql> explain update keyvalue set cnt = 1 where id = 1\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: keyvalue
type: range <--------------------
possible_keys: PRIMARY
key: PRIMARY
key_len: 2
ref: const
rows: 1
Extra: Using where
1 row in set (0.01 sec)
Imho - "const" is used when you compare all parts of a PRIMARY KEY or UNIQUE index to constant values..
[2 Aug 2013 1:04]
kazushige uratani
Hi Umesh Thank you for your reply as soon as possible. I am looking forward that the bug is fixed Thanks, kazushige uratani
[2 Aug 2013 1:12]
kazushige uratani
Hi Umesh I confirm the following information. The optimizer trace could print ranges for key parts that were not usable for range access. (Bug #14615536) Thanks, kazushige uratani
[5 Aug 2013 1:23]
kazushige uratani
Hi Umesh
How can you verify that it is correct then you will need to download the "mysql5.6.13",
Had not been fixed.
How do you think have been fixed in this "mysql5.6.13" bug,
Is it not been fixed yet?
kazushige uratani
The following procedure will be
mysql> \s
--------------
/var/lib/mysql5613/bin/mysql Ver 14.14 Distrib 5.6.13, for Linux (x86_64) using EditLine wrapper
Connection id: 7
Current database: sqa_knight
Current user: root@localhost
SSL: Not in use
Current pager: stdout
Using outfile: ''
Using delimiter: ;
Server version: 5.6.13 Source distribution
Protocol version: 10
Connection: Localhost via UNIX socket
Server characterset: utf8
Db characterset: utf8
Client characterset: utf8
Conn. characterset: utf8
UNIX socket: /var/lib/mysql5613/mysql5613.sock
Uptime: 4 hours 52 min 36 sec
Threads: 1 Questions: 4369 Slow queries: 0 Opens: 78 Flush tables: 1 Open tables: 71 Queries per second avg: 0.248
--------------
mysql> CREATE TABLE `tbl_sample` (
-> `id` smallint(5) unsigned NOT NULL DEFAULT '0',
-> `name` varchar(30) DEFAULT NULL,
-> `cnt` tinyint(3) unsigned NOT NULL DEFAULT '0',
-> PRIMARY KEY (`id`)
-> ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.02 sec)
mysql> desc tbl_sample;
+-------+----------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+----------------------+------+-----+---------+-------+
| id | smallint(5) unsigned | NO | PRI | 0 | |
| name | varchar(30) | YES | | NULL | |
| cnt | tinyint(3) unsigned | NO | | 0 | |
+-------+----------------------+------+-----+---------+-------+
3 rows in set (0.00 sec)
mysql> show index from tbl_sample;
+------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| tbl_sample | 0 | PRIMARY | 1 | id | A | 0 | NULL | NULL | | BTREE | | |
+------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
1 row in set (0.00 sec)
mysql> explain update tbl_sample set cnt = 1 where id = 1\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: tbl_sample
type: range
possible_keys: PRIMARY
key: PRIMARY
key_len: 2
ref: const
rows: 1
Extra: Using where
1 row in set (0.00 sec)
[5 Aug 2013 1:40]
kazushige uratani
Hi Umesh
Is additional information.
If you look at the "SELECT"
It becomes the "const" "type" is.
mysql> explain select * from tbl_sample where id = 1\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: tbl_sample
type: const
possible_keys: PRIMARY
key: PRIMARY
key_len: 2
ref: const
rows: 1
Extra: NULL
1 row in set (0.00 sec)
kazushige uratani
[5 Aug 2013 8:02]
Jørgen Løland
Thank you for the bug report. EXPLAIN is actually correct here - execution of the statement uses 'range' access, not 'ref' or 'const'. The reason is that single-table UPDATE/DELETE statements go through only a subset of the optimizations that SELECT and multi-table UPDATE/DELETE statements go through. One of the optimizations not performed is detection of 'const' tables in the case of constant equality predicates applied to unique indexes. MySQL has always behaved this way, it's just that it became more apparent now that EXPLAIN can handle UPDATE and DELETE statements. Fixing it is non-trivial, but we'll definitely keep it on the todo-list. I'm changing this bug report to feature request.
[5 Aug 2013 16:03]
kazushige uratani
Hi. If the current "update", I understand that "range" is correct. Thank you for your answers more quickly. kazushige uratani.
[10 Mar 2016 9:02]
Valeriy Kravchuk
There is an interesting case of the same bug with multiple-column index involved, like this:
mysql> show create table tr\G
*************************** 1. row ***************************
Table: tr
Create Table: CREATE TABLE `tr` (
`id` int(11) NOT NULL DEFAULT '0',
`c1` int(11) DEFAULT NULL,
`c2` int(11) DEFAULT NULL,
`val` int(11) DEFAULT NULL,
KEY `k` (`id`,`c1`,`c2`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)
mysql> explain select val from tr where id = 1 and c1 = 1 and c2 = 1;
+----+-------------+-------+------+---------------+------+---------+------------
-------+------+-------+
| id | select_type | table | type | possible_keys | key | key_len | ref
| rows | Extra |
+----+-------------+-------+------+---------------+------+---------+------------
-------+------+-------+
| 1 | SIMPLE | tr | ref | k | k | 14 | const,const
,const | 1 | NULL |
+----+-------------+-------+------+---------------+------+---------+------------
-------+------+-------+
1 row in set (0.00 sec)
mysql> explain update tr set val=1 where id = 1 and c1 = 1 and c2 = 1;
+----+-------------+-------+-------+---------------+------+---------+-----------
--------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref
| rows | Extra |
+----+-------------+-------+-------+---------------+------+---------+-----------
--------+------+-------------+
| 1 | SIMPLE | tr | range | k | k | 14 | const,cons
t,const | 1 | Using where |
+----+-------------+-------+-------+---------------+------+---------+-----------
--------+------+-------------+
1 row in set (0.01 sec)
So, "ref' access for SELECT, but "range" access for UPDATE.
Moreover, it seems in case of InnoDB table range access leads to extra row locked (like row with (1,1,2) locked when we ask for (1,1,1)).
[10 Mar 2016 12:41]
Valeriy Kravchuk
We have extra gap lock before the next key as a result:
mysql> show create table tr\G
*************************** 1. row ***************************
Table: tr
Create Table: CREATE TABLE `tr` (
`id` int(11) NOT NULL DEFAULT '0',
`c1` int(11) DEFAULT NULL,
`c2` int(11) DEFAULT NULL,
`val` int(11) DEFAULT NULL,
KEY `k` (`id`,`c1`,`c2`),
KEY `k2` (`c2`,`id`,`c1`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)
mysql> alter table tr drop key k2;
Query OK, 0 rows affected (0.02 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from tr;
+----+------+------+------+
| id | c1 | c2 | val |
+----+------+------+------+
| 1 | 1 | 1 | 0 |
| 1 | 1 | 2 | 0 |
| 1 | 1 | 3 | 0 |
+----+------+------+------+
3 rows in set (0.00 sec)
mysql> update tr set val=1 where id = 1 and c1 = 1 and c2 = 1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> show engine innodb status\G
...
------------
TRANSACTIONS
------------
Trx id counter 163215
Purge done for trx's n:o < 163214 undo n:o < 0 state: running but idle
History list length 72
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 163206, not started
MySQL thread id 4, OS thread handle 0xac0, query id 86 localhost 127.0.0.1 root
cleaning up
---TRANSACTION 163214, ACTIVE 18 sec
4 lock struct(s), heap size 1184, 3 row lock(s), undo log entries 1
MySQL thread id 3, OS thread handle 0xbc4, query id 93 localhost 127.0.0.1 root
init
show engine innodb status
Trx read view will not see trx with id >= 163215, sees < 163215
TABLE LOCK table `test`.`tr` trx id 163214 lock mode IX
RECORD LOCKS space id 504 page no 4 n bits 72 index `k` of table `test`.`tr` trx
id 163214 lock_mode X
Record lock, heap no 2 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
0: len 4; hex 80000001; asc ;;
1: len 4; hex 80000001; asc ;;
2: len 4; hex 80000001; asc ;;
3: len 6; hex 000000001000; asc ;;
RECORD LOCKS space id 504 page no 3 n bits 72 index `GEN_CLUST_INDEX` of table `
test`.`tr` trx id 163214 lock_mode X locks rec but not gap
Record lock, heap no 2 PHYSICAL RECORD: n_fields 7; compact format; info bits 0
0: len 6; hex 000000001000; asc ;;
1: len 6; hex 000000027d8e; asc } ;;
2: len 7; hex 7f000002660110; asc f ;;
3: len 4; hex 80000001; asc ;;
4: len 4; hex 80000001; asc ;;
5: len 4; hex 80000001; asc ;;
6: len 4; hex 80000001; asc ;;
RECORD LOCKS space id 504 page no 4 n bits 72 index `k` of table `test`.`tr` trx
id 163214 lock_mode X locks gap before rec
Record lock, heap no 3 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
0: len 4; hex 80000001; asc ;;
1: len 4; hex 80000001; asc ;;
2: len 4; hex 80000002; asc ;;
3: len 6; hex 000000001001; asc ;;
...
[11 Mar 2016 15:48]
Matthew Boehm
I discovered this issue myself yesterday on TokuDB, indicating, as noted above, this isn't engine specific and is an issue optimizing the query. Tacking this on for documentation:
[PlatformDev]> explain UPDATE Cookies SET last_active = NULL WHERE id = 39161496;
+------+-------------+---------+-------+---------------+---------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key |
key_len | ref | rows | Extra |
+------+-------------+---------+-------+---------------+---------+---------+------+------+-------------+
| 1 | SIMPLE | Cookies | range | PRIMARY | PRIMARY | 4 | NULL | 1 | Using where |
+------+-------------+---------+-------+---------------+---------+---------+------+------+-------------+
1 row in set (0.07 sec)
[PlatformDev]> SHOW CREATE TABLE
`EmaticPlatformDev`.`Cookies`\G
*************************** 1. row ***************************
Table: Cookies
Create Table: CREATE TABLE `Cookies` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`apikey_id` int(11) DEFAULT NULL,
`uuid` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
`email` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
`last_active` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE
CURRENT_TIMESTAMP,
`created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (`id`),
UNIQUE KEY `cookies_account_id_uuid` (`apikey_id`,`uuid`),
KEY `cookies_email` (`email`) USING BTREE
) ENGINE=TokuDB AUTO_INCREMENT=39277383 DEFAULT CHARSET=utf8
COLLATE=utf8_unicode_ci
1 row in set (0.00 sec)
[PlatformDev]> explain format=json UPDATE Cookies SET last_active = NULL WHERE id = 39161496;
| EXPLAIN
| {
"query_block": {
"select_id": 1,
"table": {
"update": 1,
"table_name": "Cookies",
"access_type": "range",
"possible_keys": ["PRIMARY"],
"key": "PRIMARY",
"key_length": "4",
"used_key_parts": ["id"],
"rows": 1,
"attached_condition": "(Cookies.`id` = 39161496)"
}
}
}
Description: I have a question about the "EXPLAIN" of "update". When run the "EXPLAIN" of "update" In spite of the "PRIMARY" "key" is, It's become a "range" "type" is, but right? If the "PRIMARY" "key" is, I thought I would be a "const" "type" is. Thanks in advance. How to repeat: mysql> CREATE TABLE `tbl_sample` ( `id` smallint(5) unsigned NOT NULL DEFAULT '0', `name` varchar(30) DEFAULT NULL, `cnt` tinyint(3) unsigned NOT NULL DEFAULT '0', PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; mysql> desc tbl_sample; +-------+----------------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+----------------------+------+-----+---------+-------+ | id | smallint(5) unsigned | NO | PRI | 0 | | | name | varchar(30) | YES | | NULL | | | cnt | tinyint(3) unsigned | NO | | 0 | | +-------+----------------------+------+-----+---------+-------+ 3 rows in set (0.03 sec) mysql> show index from tbl_sample; +------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | +------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ | tbl_sample | 0 | PRIMARY | 1 | id | A | 0 | NULL | NULL | | BTREE | | | +------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ 1 row in set (0.00 sec) mysql> explain update tbl_sample set cnt = 1 where id = 1\G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: tbl_sample type: range possible_keys: PRIMARY key: PRIMARY key_len: 2 ref: NULL rows: 1 Extra: Using where 1 row in set (0.03 sec)