|
Hi,
During UPDATE, the Buffer algorithm should be used only when a condition in the WHERE clause will be changed during the UPDATE.
It seems there's a bug with partitioned tables where the buffer algorithm is used even when the value is not changed.
How to repeat :
CREATE TABLE `sc_param_index` (
|
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
|
`gen_subtype_id` tinyint(3) unsigned NOT NULL,
|
PRIMARY KEY (`id`,`gen_subtype_id`)
|
) ENGINE=InnoDB AUTO_INCREMENT=586511719 DEFAULT CHARSET=utf8 ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=8
|
PARTITION BY LIST (`gen_subtype_id`)
|
(PARTITION `p0` VALUES IN (1) ENGINE = InnoDB,
|
PARTITION `p1` VALUES IN (2) ENGINE = InnoDB
|
)
|
|
EXPLAIN PARTITIONS UPDATE sc_param_index SET id=258376819, gen_subtype_id=1 WHERE `id` = 258376819 AND `gen_subtype_id` = 1\G
|
*************************** 1. row ***************************
|
id: 1
|
select_type: SIMPLE
|
table: sc_param_index
|
partitions: p0
|
type: range
|
possible_keys: PRIMARY
|
key: PRIMARY
|
key_len: 5
|
ref: NULL
|
rows: 1
|
Extra: Using where; Using buffer
|
1 row in set (0.01 sec)
|
=> Using buffer here, but gen_subtype_id is not changed!
EXPLAIN PARTITIONS UPDATE sc_param_index SET id=258376819 WHERE `id` = 258376819 AND `gen_subtype_id` = 1\G
|
*************************** 1. row ***************************
|
id: 1
|
select_type: SIMPLE
|
table: sc_param_index
|
partitions: p0
|
type: range
|
possible_keys: PRIMARY
|
key: PRIMARY
|
key_len: 5
|
ref: NULL
|
rows: 1
|
Extra: Using where
|
1 row in set (0.00 sec)
|
gen_subtype_id removed from the SET, no more Using buffer.
Now without partition :
DROP TABLE IF EXISTS sc_param_index;
|
|
CREATE TABLE `sc_param_index` (
|
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
|
`gen_subtype_id` tinyint(3) unsigned NOT NULL,
|
PRIMARY KEY (`id`,`gen_subtype_id`)
|
) ENGINE=InnoDB AUTO_INCREMENT=586511719 DEFAULT CHARSET=utf8 ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=8;
|
|
EXPLAIN PARTITIONS UPDATE sc_param_index SET id=258376819, gen_subtype_id=1 WHERE `id` = 258376819 AND `gen_subtype_id` = 1\G
|
*************************** 1. row ***************************
|
id: 1
|
select_type: SIMPLE
|
table: sc_param_index
|
partitions: NULL
|
type: range
|
possible_keys: PRIMARY
|
key: PRIMARY
|
key_len: 5
|
ref: NULL
|
rows: 1
|
Extra: Using where
|
1 row in set (0.00 sec)
|
No Using buffer even with gen_subtype_id=1 in the SET, that's the expected behaviour.
EXPLAIN PARTITIONS UPDATE sc_param_index SET id=258376819 WHERE `id` = 258376819 AND `gen_subtype_id` = 1\G
|
*************************** 1. row ***************************
|
id: 1
|
select_type: SIMPLE
|
table: sc_param_index
|
partitions: NULL
|
type: range
|
possible_keys: PRIMARY
|
key: PRIMARY
|
key_len: 5
|
ref: NULL
|
rows: 1
|
Extra: Using where
|
1 row in set (0.00 sec)
|
|