How to Bulk Update Large MySQL Tables Efficiently

Updating millions of rows in a large MySQL table can severely degrade database performance, cause replication lag, and lock tables for extended periods. To prevent these issues, developers must use optimized strategies rather than running a single, massive UPDATE query. This article covers the most efficient techniques for performing bulk updates in MySQL, including batching, leveraging temporary tables, and utilizing INSERT ... ON DUPLICATE KEY UPDATE statements.

1. Batching Updates in Chunks

The most common and safest way to update a massive table is by dividing the workload into smaller, manageable chunks. Running updates in batches of 1,000 to 10,000 rows prevents long-term row locks and keeps the database responsive.

Instead of using LIMIT with an offset, which becomes slow on large datasets, iterate through the table using the primary key:

UPDATE users 
SET status = 'active' 
WHERE id BETWEEN 1 AND 10000 
  AND status = 'pending';

In your application code, loop through the primary key ranges (e.g., 1–10,000, 10,001–20,000) until all rows are processed. Introduce a tiny sleep delay (e.g., 50–100 milliseconds) between batches to allow other queries to execute.

2. Using Temporary Tables and JOINs

If you need to update rows with different, specific values for each row, sending individual UPDATE statements is highly inefficient. Instead, load the new data into a temporary table and perform a single bulk JOIN update.

First, create a temporary table and insert the new data:

CREATE TEMPORARY TABLE temp_user_status (
    user_id INT PRIMARY KEY,
    new_status VARCHAR(50)
);

-- Bulk insert the update data
INSERT INTO temp_user_status (user_id, new_status) VALUES 
(1, 'active'), 
(2, 'suspended'), 
(3, 'pending');

Then, execute a single JOIN update:

UPDATE users u
JOIN temp_user_status t ON u.id = t.user_id
SET u.status = t.new_status;

This method is incredibly fast because it utilizes MySQL’s internal join optimization and reduces round-trip network latency between your application and the database.

3. INSERT … ON DUPLICATE KEY UPDATE

When you have a mix of new rows to insert and existing rows to update, use the INSERT ... ON DUPLICATE KEY UPDATE (IODKU) statement. This is a highly optimized, single-query solution.

INSERT INTO users (id, status, last_login) VALUES 
(1, 'active', NOW()),
(2, 'suspended', NOW())
ON DUPLICATE KEY UPDATE 
status = VALUES(status),
last_login = VALUES(last_login);

If the primary key (or a unique key) already exists, MySQL will update the specified columns for those rows instead of inserting new ones.

4. Optimize Transaction and Index Overhead

Every time you update a row, MySQL must write to the transaction log (redo log) and update any affected indexes. You can speed up bulk updates with the following practices: