Description:
In a BEFORE INSERT of BEFORE UPDATE trigger, trigger field belongs to the NEW row, e.g., NEW.a, NEW.b, are all marked in write_set no matter the field will be written or read.
That will cause an unnecessary temporary table in UPDATE command in some scenarios.
Example:
create table t1 (a int primary key, b int);
create table t2 (a int, b int);
CREATE TRIGGER t1_before_update BEFORE UPDATE
ON t1 FOR EACH ROW
INSERT INTO t2 VALUES (NEW.a, NEW.b);
mysql> explain update t1 set b=10;
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-----------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-----------------+
| 1 | UPDATE | t1 | NULL | index | NULL | PRIMARY | 4 | NULL | 1 | 100.00 | Using temporary |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-----------------+
1 row in set, 1 warning (0.00 sec)
Temporary table is usually used when updating a key used to search with.
But, primary key (column a) is not modified in UPDATE command or the associate trigger. We only read the primary key (column a) of t1 in trigger.
Temporary table is not necessary here.
How to repeat:
As above
Suggested fix:
The unexpected temporary table is used because all field of NEW row in before trigger is marked to write_set no matter it will be read or write.
In function "sp_head::mark_used_trigger_fields", field of NEW or OLD row is marked to read_set and write_set:
sql/sp_head.cc:
void sp_head::mark_used_trigger_fields(TABLE *subject_table) {
for (SQL_I_List<Item_trigger_field> *trig_field_list =
m_list_of_trig_fields_item_lists.first;
trig_field_list;
trig_field_list = trig_field_list->first->next_trig_field_list) {
for (Item_trigger_field *f = trig_field_list->first; f;
f = f->next_trg_field) {
if (f->field_idx == (uint)-1) {
// We cannot mark fields which does not present in table.
continue;
}
bitmap_set_bit(subject_table->read_set, f->field_idx);
if (f->get_settable_routine_parameter()) // the condition
bitmap_set_bit(subject_table->write_set, f->field_idx); // write_set is set here
}
}
}
sql/item.h, Detail of condition, inside class Item_trigger_field:
Settable_routine_parameter *get_settable_routine_parameter() override {
return (read_only ? 0 : this);
}
"Trigger field is read-only unless it belongs to the NEW row in a BEFORE INSERT of BEFORE UPDATE trigger." notes in code.
In a before trigger, if "set NEW.a=NEW.b" exists, we should mark a column to write_set, not the b column, but now a and b are both marked in write_set.
Item_trigger_field has another member called "want_privilege", as notes in code, "'want_privilege' holds privileges required to perform operation on this trigger field (SELECT_ACL if we are going to read it and UPDATE_ACL if we are going to update it). "
So I think we can use want_privilege to make the write_set more accurate.
1. Add a public interface to Item_trigger_field:
ulong Item_trigger_field::get_want_privilege() {
return want_privilege;
}
2. Add a judge before mark write_set in sp_head::mark_used_trigger_fields:
if (f->get_settable_routine_parameter() && && f->get_want_privilege() > SELECT_ACL) // the condition
bitmap_set_bit(subject_table->write_set, f->field_idx); // write_set is set here
}
Make the change and rebuild it, we can get a better plan.
mysql> explain update t1 set b=10;
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------+
| 1 | UPDATE | t1 | NULL | index | NULL | PRIMARY | 4 | NULL | 1 | 100.00 | NULL |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------+
1 row in set, 1 warning (0.02 sec)
Description: In a BEFORE INSERT of BEFORE UPDATE trigger, trigger field belongs to the NEW row, e.g., NEW.a, NEW.b, are all marked in write_set no matter the field will be written or read. That will cause an unnecessary temporary table in UPDATE command in some scenarios. Example: create table t1 (a int primary key, b int); create table t2 (a int, b int); CREATE TRIGGER t1_before_update BEFORE UPDATE ON t1 FOR EACH ROW INSERT INTO t2 VALUES (NEW.a, NEW.b); mysql> explain update t1 set b=10; +----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-----------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-----------------+ | 1 | UPDATE | t1 | NULL | index | NULL | PRIMARY | 4 | NULL | 1 | 100.00 | Using temporary | +----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-----------------+ 1 row in set, 1 warning (0.00 sec) Temporary table is usually used when updating a key used to search with. But, primary key (column a) is not modified in UPDATE command or the associate trigger. We only read the primary key (column a) of t1 in trigger. Temporary table is not necessary here. How to repeat: As above Suggested fix: The unexpected temporary table is used because all field of NEW row in before trigger is marked to write_set no matter it will be read or write. In function "sp_head::mark_used_trigger_fields", field of NEW or OLD row is marked to read_set and write_set: sql/sp_head.cc: void sp_head::mark_used_trigger_fields(TABLE *subject_table) { for (SQL_I_List<Item_trigger_field> *trig_field_list = m_list_of_trig_fields_item_lists.first; trig_field_list; trig_field_list = trig_field_list->first->next_trig_field_list) { for (Item_trigger_field *f = trig_field_list->first; f; f = f->next_trg_field) { if (f->field_idx == (uint)-1) { // We cannot mark fields which does not present in table. continue; } bitmap_set_bit(subject_table->read_set, f->field_idx); if (f->get_settable_routine_parameter()) // the condition bitmap_set_bit(subject_table->write_set, f->field_idx); // write_set is set here } } } sql/item.h, Detail of condition, inside class Item_trigger_field: Settable_routine_parameter *get_settable_routine_parameter() override { return (read_only ? 0 : this); } "Trigger field is read-only unless it belongs to the NEW row in a BEFORE INSERT of BEFORE UPDATE trigger." notes in code. In a before trigger, if "set NEW.a=NEW.b" exists, we should mark a column to write_set, not the b column, but now a and b are both marked in write_set. Item_trigger_field has another member called "want_privilege", as notes in code, "'want_privilege' holds privileges required to perform operation on this trigger field (SELECT_ACL if we are going to read it and UPDATE_ACL if we are going to update it). " So I think we can use want_privilege to make the write_set more accurate. 1. Add a public interface to Item_trigger_field: ulong Item_trigger_field::get_want_privilege() { return want_privilege; } 2. Add a judge before mark write_set in sp_head::mark_used_trigger_fields: if (f->get_settable_routine_parameter() && && f->get_want_privilege() > SELECT_ACL) // the condition bitmap_set_bit(subject_table->write_set, f->field_idx); // write_set is set here } Make the change and rebuild it, we can get a better plan. mysql> explain update t1 set b=10; +----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------+ | 1 | UPDATE | t1 | NULL | index | NULL | PRIMARY | 4 | NULL | 1 | 100.00 | NULL | +----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------+ 1 row in set, 1 warning (0.02 sec)