Estimating Progress of Large UPDATE Statements in MySQL Using Performance Schema

When an accidental large UPDATE is executed on a MySQL table, killing the thread can cause a long rollback, and leaving it running leaves the duration unknown. The article explores whether the progress of such an UPDATE can be measured.

The author creates a test database, inserts tens of millions of rows, and then runs a heavyweight UPDATE that modifies the primary key. In a separate session, they query performance_schema to see the number of rows the engine has examined.

The observation shows that rows_examined reported by performance_schema is roughly twice the total number of rows in the table when the primary key is updated. Therefore, the progress can be estimated as (rows_examined) / (2 × total_rows) .

A second experiment updates only non‑key columns. In this case, rows_examined equals the total row count, indicating a different coefficient. The article discusses how to determine the appropriate multiplier based on the UPDATE’s WHERE clause, primary‑key changes, or unique‑key modifications.

Additional tips include using information_schema.tables to obtain an inexpensive estimate of table row count instead of running SELECT COUNT(1) , and testing the coefficient on a smaller table with the same schema to improve accuracy.

By combining these observations, developers can approximate the progress of a mistakenly large UPDATE and make more informed decisions about whether to wait, kill, or intervene.