How to Update Millions of Records in MySQL

How to update millions of rows quickly in mysql

Use an "Update-Select". I think it should be the fastest way to update many rows. Therefore see this question:
MySQL - UPDATE query based on SELECT Query

How to Update 1 Million of rows quickly in MYSQL

I also encountered this problem before. That was around 2009 or 2010 where I need to update multiple entries fast because of the request timeout. Some solution I found was to extend the request timeout configuration of the server but another solution I found was the create a temporary table on the database server and relate the two tables but unfortunately, I cannot share with you the link because that was a long time ago.
So here what I did as long as I could recall:

1. Create a temporary table with similar structure to your CSV bearing the primary key to which relates to the table you like to perform the update. I know MySQL has the temporary table capability.
2. From your CSV, upload data by insert the record at once (by batch or multiple values on INSERT statement). This will be fast
3. Once you have inserted all the record, relate the temporary to your table on UPDATE statement.
4. Example: UPDATE orig_table ot, temp_table tt SET ot.URLRedirect=tt.URLRedirect WHERE ot.ID=tt.ID
5. Destroy your temporary table.

The UPDATE statement will set the value of orig_table to the new value from temp_table provided that they relate through their ID.

Update about 1 million rows in MySQL table every 1 hour

Apart from the fact that your code is a little bit confusing ($PER_part2 and $PER_part3 are unused) i would do the following:

The script will not work with result_array for more than 1 million iterations.
The reason is, result_array stores all data in an array - and sooner or later you'll get a memory limit problem.

In order to avoid this you have to use unbuffered_row.
This method returns a single result row without prefetching the whole result in memory.

Take a look at their documentation here. (section unbuffered_row)

The next thing i would change are your if blocks - i would use the if/else syntax here. (it isn't that big of a difference but considering your amount of rows - it might helps)

Also, i outsourced the block where you calculate your rating twice with the same logic behind.

Basically the following should work:

function rows_update() 
{
    $currency_numbers_after_dot = $this->currencies_model->get_currency('ONE', 'My currencty')['numbers_after_dot'];
    $game_currency_percentage_max = $this->settings_model->get_settings_details('game_rating_percentage_max')['value'];
    $game_currency_speed_in_hour = $this->settings_model->get_settings_details('game_currency_speed_in_hour')['value'];
    $this->db->from('members');
    $query = $this->db->get();
    
    $arrUpdateBatchData = [];
    
    while ($row = $query->unbuffered_row('array'))
    {
        $game_total_balance = round($row['game_vault_balance'] + $row['game_available_balance'], $currency_numbers_after_dot);
        if ($game_total_balance > 0) {
            // Game Rating Calculate
            // Rating Part1
            // Rating Part1_1
            $rating_part1_1 = $this->getRatingPart($row['game_vault_balance']);
            $rating_part1_2 = $this->getRatingPart($game_total_balance);
            // Rating part1_3
            $rating_part1_3 = 0;
            // Rating part1_4
            $rating_part1_4 = 0;
            // Rating part2
            $PER_part2 = '0';
            // Rating part3
            $PER_part3 = '0';
            // Calculate all rating
            $rating = round($rating_part1_1 + $rating_part1_2 + $rating_part1_3 + $rating_part1_4 + $rating_part2 + $rating_part3, 2);
            if ($rating <= 1) {
                $rating_member = $rating;
            }
            elseif ($rating > 1) {
                $rating_member = floor($rating);
            }
            
            // Game balance calculate
            $amount_from_game_vault_in_hour = $game_currency_speed_in_hour / '100' * $row['game_vault_balance'];
            $new_balance_in_hour = ($game_currency_percentage_max / '100') * $row['rating'] * $amount_from_game_vault_in_hour;
            $game_balance_in_hour_amount = $amount_from_game_vault_in_hour + $new_balance_in_hour;
            
            $arrUpdateData = [
                'UserID' => $row['UserID'], 
                'game_vault_balance' => ($row['game_vault_balance'] - $amount_from_game_vault_in_hour),
                'game_available_balance' => ($row['game_available_balance'] - $game_balance_in_hour_amount),
                'rating' => $rating_member,
                'game_rating_and_balance_update_last_time' => date('Y-m-d H:i:s')
            ];
            
            $arrUpdateBatchData[] = $arrUpdateData;
            
        }
        
        if (count($arrUpdateBatchData) > 500)
        {
            $this->db->update_batch('members', $arrUpdateBatchData, 'UserID');
            $arrUpdateBatchData = [];
        }       
    }
    
    //update last items
    if (count($arrUpdateBatchData) > 0)
    {
        $this->db->update_batch('members', $arrUpdateBatchData, 'UserID');
        $arrUpdateBatchData = [];
    }
    return;    
}

function getRatingPart($val)
{
    if ($val == 0) {
        $rating_part = 0; 
    }
    elseif ($val > 0 AND $val < 20) 
    {
        $rating_part = '0.1'; 
    }
    elseif ($val > 20 AND $val < 2000) 
    {
    
        $max_game_vault_balance = 2000;
        $percent = floor($val * 100 / $max_game_vault_balance);
        $additional_rating = 0.05 * $percent / 100;
           
        $rating_part = round(0.1 + $additional_rating, 2); 

    }
    else {
        $rating_part = 0.15; 
    }
    
    return $rating_part;
}

Updating 40 million mysql cells at once

The syntax for it would be something like this:

INSERT INTO much_larger_table (cols...)
  SELECT cols... FROM a_table
  WHERE ...conditions...;

(I am using some placeholders because you haven't described your tables.)

There's no limit to the number of rows you can copy using syntax like this, but there's a practical limit to how many you want to do in one batch.

It will take time of course. We can't predict how long it will take, because it depends on a lot of factors specific to your server and your tables. It's possible that it will take more than the hour in between your scheduled tasks.

The risk is that an INSERT...SELECT statement like this locks the rows as they are read from the origin table. This means no session can modify those rows, and maybe cannot even insert or update other rows in that table, depending on gap locking.

I tell the developers at my company to limit batches to 1000 rows per transaction. This allows each transaction to finish quickly, reduces the number of rows locked and the duration of the lock, and allows MySQL garbage collection to continue.

---

Related Topics