When Does MySQL Actually Update Index Blocks? A Deep Dive into InnoDB Update Mechanics

Problem Description

Assume a table mytest with a primary key and two secondary indexes (c1 and c2) and a single row (id=1, c1=11, c2=12, c3=13).

CREATE TABLE `mytest` (
  `id` int(11) NOT NULL,
  `c1` int(11) DEFAULT NULL,
  `c2` int(11) DEFAULT NULL,
  `c3` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `c1` (`c1`),
  KEY `c2` (`c2`)
);
+----+------+------+------+
| id | c1   | c2   | c3   |
+----+------+------+------+
|  1 |  11  |  12  |  13  |
+----+------+------+------+

The table has three indexes:

Primary key index (contains a block)

Secondary index c1 (contains a block)

Secondary index c2 (contains a block)

Consider three UPDATE statements:

A: update mytest set c1=11,c2=12,c3=13 where id=1 (no column value actually changes)

B: update mytest set c1=11,c2=12,c3=14 where id=1 (c1 and c2 unchanged)

C: update mytest set c1=12,c2=12,c3=14 where id=1 (c2 unchanged)

Questions:

In scenario A, do any index blocks change?

In scenario B, do the c1 and c2 secondary indexes change?

In scenario C, does the c2 secondary index change?

Rough Update Process

The mysql_update function follows roughly these steps:

Scan the row (rr_sequential) and store the MySQL‑format data in record[0].

Copy the original data to record[1].

Fill record[0] with the new values parsed from the UPDATE statement (fill_record_n_invoke_before_triggers → fill_record). field1 | field2 | … | fieldN Each field holds a pointer to the actual data.

Push column names and values into column_list and value_list (shown in the Bison rule).

$$.column_list->push_back($1.column) ||
                $$.value_list->push_back($1.value)

Debugging with GDB after the fill_record step shows the fields and values:

(gdb) p fields
$67 = (List<Item> &) @0x7fff30005da8: {first = 0x7fff300067f8, last = 0x7fff30006af8, elements = 3}
(gdb) p ((Item_field *)(fields->first->info)).field_name
$68 = 0x7fff309316d4 "c1"
(gdb) p ((Item_field *)(fields->first->next->info)).field_name
$69 = 0x7fff309316d7 "c2"
(gdb) p ((Item_field *)(fields->first->next->next->info)).field_name
$70 = 0x7fff309316da "c3"
(gdb) p values
$73 = (List<Item> &) @0x7fff30006e38: {first = 0x7fff30006808, last = 0x7fff30006b08, elements = 3}
(gdb) p ((Item_int*)(values->first->info)).value
$74 = 11
(gdb) p ((Item_int*)(values->first->next->info)).value
$75 = 12
(gdb) p ((Item_int*)(values->first->next->next->info)).value
$76 = 13

After this step record[0] contains the new values.

Filter point 1 : Compare record[0] and record[1]. If they are identical, the UPDATE is skipped. This corresponds to scenario A.

If differences exist, the changed fields and their numbers are placed into m_prebuilt->upd_node->update (calc_row_difference). The comparison checks:

Whether the length changed (len).

Whether the actual bytes differ (memcmp result).

Confirm whether any changed field belongs to a secondary index.

If the statement is DELETE, all secondary indexes are affected.

If UPDATE, the engine checks whether the changed fields appear in the ordered secondary‑index columns. If not, the node is marked UPD_NODE_NO_ORD_CHANGE, meaning no secondary‑index modification is needed.

At this point the row format is converted to the InnoDB internal format.

Filter point 2 : If the node is UPD_NODE_NO_ORD_CHANGE, only the primary key is updated. This matches scenario B – c3 changes but c1 and c2 remain the same, so only the primary index block is touched.

If secondary indexes need changes, the engine iterates over each secondary index.

Filter point 3 : For each secondary index, verify whether any of the changed fields are part of that index. If a field is not in the index, the index is skipped.

If the field is not in m_prebuilt->upd_node->update, continue to the next field.

If it is present, call dfield_datas_are_binary_equal to compare old and new binary values.

Scenario C illustrates that c2 does not change, so the c2 index block stays untouched, while the primary index and c1 index are updated.

Conclusion

A: No data modification – all index blocks remain unchanged.

B: Only the primary key index block changes; secondary indexes c1 and c2 stay the same.

C: Primary key index block and c1 secondary index block change; c2 index block stays unchanged.

Verification

To verify, the following steps were performed:

Create test tables mytest and mytest2 and insert sample rows.

CREATE TABLE `mytest` (
  `id` int(11) NOT NULL,
  `c1` int(11) DEFAULT NULL,
  `c2` int(11) DEFAULT NULL,
  `c3` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `c1` (`c1`),
  KEY `c2` (`c2`)
) ENGINE=InnoDB;
INSERT INTO mytest VALUES (1,11,12,13), (2,14,15,16), ... ;
CREATE TABLE `mytest2` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `c1` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB;
INSERT INTO mytest2(c1) VALUES (1);

Record the current LSN (Log Sequence Number) – initially 4806780238 with no dirty pages.

Query INNODB_SYS_TABLES and INNODB_SYS_INDEXES to obtain the INDEX_IDs:

PRIMARY: 10957

c1: 10958

c2: 10959

Use the innblock tool to scan the .ibd file and locate the blocks for each index.

./innblock mytest.ibd scan 16
===INDEX_ID:10957
level0 total block is (1)
block_no: 3,level:   0|*
===INDEX_ID:10958
level0 total block is (1)
block_no: 4,level:   0|*
===INDEX_ID:10959
level0 total block is (1)
block_no: 5,level:   0|*

Inspect each block's last_modify_lsn:

./innblock mytest.ibd 3 16
... last_modify_lsn:4806771220 ...
./innblock mytest.ibd 4 16
... last_modify_lsn:4806771252 ...
./innblock mytest.ibd 5 16
... last_modify_lsn:4806771284 ...

Generate load on the server by inserting many rows into mytest2 to advance the LSN, then perform an UPDATE on mytest that changes only c3 (scenario B) and another that changes c1 and c3 (scenario C).

After the UPDATE, re‑inspect the three blocks. In scenario B the LSN of block 4 (c1) stays at 4806771252, confirming that the c1 index was not modified, while the primary block's LSN moves to the new value. In scenario C both block 3 (PRIMARY) and block 4 (c1) show updated LSNs, whereas block 5 (c2) remains unchanged.

Verification screenshots (core to the analysis) are shown below:

Code Flow Overview

mysql_update
  -> rr_sequential            // read original row into record[0]
  -> fill_record_n_invoke_before_triggers
       -> fill_record          // apply new column values to record[0]
  -> compare_records          // filter point 1: skip if unchanged
  -> handler::ha_update_row
       -> ha_innobase::update_row
            -> calc_row_difference   // build upd_node->update array
                 // check length change or memcmp difference
            -> row_update_for_mysql
                 -> row_upd_step
                      -> row_upd
                           // decide whether secondary indexes need changes
                           // filter point 2: UPD_NODE_NO_ORD_CHANGE skips secondary updates
                           // if needed, iterate each secondary index
                           // filter point 3: field‑in‑index check
                           // compare old/new binary values
                           // call row_upd_sec_index_entry if change required

The detailed flow matches the three test scenarios described above.