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

1. Problem Description

Given a table mytest with a primary key id and secondary indexes on c1 and c2, three UPDATE scenarios are considered:

A: update mytest set c1=11,c2=12,c3=13 where id=1 – none of the column values actually change.

B: update mytest set c1=11,c2=12,c3=14 where id=1 – only c3 changes.

C: update mytest set c1=12,c2=12,c3=14 where id=1 – c1 changes while c2 stays the same.

The question is which index blocks (primary and secondary) are physically modified in each case.

2. Approximate Update Process in InnoDB

The function mysql_update follows roughly these steps:

Sequentially scan the row and store the original MySQL‑format data in record[0].

Parse the UPDATE statement and fill record[1] with the new values (using fill_record_n_invoke_before_triggers and fill_record). The column list and value list are built as column_list and value_list.

Compare record[0] and record[1]. If they are identical, the update is skipped – this is Filter Point 1 (scenario A).

If differences exist, calc_row_difference populates m_prebuilt->upd_node->update with the fields that need changing, checking both length changes and binary value differences.

Proceed to row_update_for_mysql, which eventually calls row_upd_step and row_upd to determine whether secondary indexes are affected.

Filter Point 2 : If no secondary‑index fields are in the update array, the node is marked UPD_NODE_NO_ORD_CHANGE and only the clustered (primary) index is updated (scenario B).

If secondary indexes may need changes, the engine iterates over each secondary index ( Filter Point 3 ) using row_upd_sec_step. For each index it checks whether any field in the update array belongs to that index and whether the binary values differ (via dfield_datas_are_binary_equal). If not, the index is left untouched; otherwise row_upd_sec_index_entry performs the actual modification (scenario C).

3. Conclusion

A: No data changes → no index blocks are modified.

B: Only the primary‑key index block changes; secondary indexes c1 and c2 remain unchanged.

C: Primary‑key index and secondary index c1 are updated; secondary index c2 stays unchanged.

4. Verification

Using the innblock tool and information_schema tables, the author identifies the INDEX_IDs:

10957 – PRIMARY (block 3)

10958 – c1 (block 4)

10959 – c2 (block 5)

Steps to validate the theory:

Create the test tables and insert initial data.

Record the current last_modify_lsn of each block.

Insert many rows into a helper table mytest2 to advance the LSN.

Execute the UPDATE statements for scenarios A, B, C.

Re‑inspect the last_modify_lsn of each block. Only the blocks expected to change show a new LSN, confirming the analysis.

Sample commands used:

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), ...;

-- scenario C example
UPDATE mytest SET c1=12,c2=12,c3=14 WHERE id=1;

After the update, the LSN values observed were:

PRIMARY block 3: new LSN around 4867604378

c1 block 4: LSN unchanged at 4806771252 (no change)

c2 block 5: new LSN around 4867604378

5. Code Flow Overview

mysql_update
  -> rr_sequential               // fetch original row into record[0]
  -> fill_record_n_invoke_before_triggers
       -> fill_record            // build column_list and value_list
  -> calc_row_difference        // populate upd_node->update array
  -> row_update_for_mysql
       -> row_upd_step
            -> row_upd          // check secondary indexes
                 -> row_upd_sec_step   // iterate each secondary index
                      -> row_upd_sec_index_entry // apply changes if needed