Updating 100,000 rows in a MySQL table can introduce significant performance issues, including prolonged table locks, elevated I/O load, and contention with concurrent database operations. Below are actionable optimization strategies to speed up these bulk update tasks.

Batch UPDATE Operations

Splitting a single large update into smaller, bounded batches reduces transaction duration, minimizes lock contention, and prevents overwhelming the database's memory and I/O resources. A typical batched update workflow uses explicit transactions and a LIMIT clause to restrict each update to a fixed number of rows:

SET autocommit = 0;
START TRANSACTION;
UPDATE customer_records SET membership_tier = 'premium' WHERE signup_date < '2023-01-01' LIMIT 8000;
COMMIT;

Wrap this SQL block in a loop (via a stored procedure, shell script, or application code) to run until the number of affected rows reaches 0, processing all target rows in manageable chunks.

Optimize Index Usage

Adding a targeted index on columns used in the UPDATE's WHERE clause allows MySQL to quickly locate and lock only the rows that need modification, rather than scanning the entire table. Avoid creating unnecessary indexes, as each additional index increases write overhead for every update operation. To add an index for the above example:

ALTER TABLE customer_records ADD INDEX idx_signup_date (signup_date);

Reduce Unnecessary Writes

Skip updating rows that already have the desired value to cut down on disk writes and lock hold time. Modify the UPDATE statement to include a condition that skips already updated rows:

UPDATE customer_records SET membership_tier = 'premium' WHERE signup_date < '2023-01-01' AND membership_tier != 'premium' LIMIT 8000;

Practical Example

For a customer_records table with 100,000 rows, the above optimized batched updates will complete significantly faster than a single large UPDATE, while reducing impact on concurrent database traffic.

Additional best practices include running large update operations during off-peak hours, and adjusting MySQL configuration parameters like innodb_buffer_pool_size and innodb_log_file_size to better handle batch write operations.