MySQL Replication: How Slave Row Search Algorithms Influence Data Lookup

The article, excerpted from the 24th chapter of "Deep Understanding of MySQL Master‑Slave Principles", examines how a MySQL replica searches for rows when applying DELETE statements, focusing on the slave_rows_search_algorithms parameter and the impact of primary or unique keys.

It starts with a background on how DML events store before‑images in DELETE_ROWS_EVENT and how the replica must locate the corresponding rows using indexes. The default binlog_row_image is FULL.

Using a concrete example table tkkk (shown with SHOW CREATE TABLE output) and a DELETE that removes rows where a=15 , the article demonstrates the cost of index lookup versus full‑table scan when the replica lacks a primary key.

Three possible search algorithms are introduced: TABLE_SCAN , INDEX_SCAN , and HASH_SCAN . Their combinations (e.g., TABLE_SCAN,INDEX_SCAN , INDEX_SCAN,HASH_SCAN , etc.) are listed, and a decision table from the source code shows which algorithm is chosen based on the presence of PK/UK, ordinary indexes, or no index.

The source code excerpts illustrate how MySQL selects the lookup method via a switch on m_rows_lookup_algorithm and assigns function pointers such as Rows_log_event::do_hash_scan_and_update , Rows_log_event::do_index_scan_and_update , or Rows_log_event::do_table_scan_and_update .

Key observations include:

Replica rows always require index lookup; without PK/UK the cost is higher.

Non‑unique indexes may need additional scans if the first matched row is not the target.

Enabling HASH_SCAN can improve performance only when the table lacks indexes or when many rows share the same key values and large DELETE/UPDATE events are processed.

If each DELETE affects only a single row, HASH_SCAN offers no benefit.

The article concludes that proper use of primary keys or unique indexes is essential to reduce replication delay, and that the slave_rows_search_algorithms setting alone does not guarantee performance gains.

Finally, readers are encouraged to consult the full 32‑lecture series for an in‑depth understanding of MySQL master‑slave mechanisms.