Hello! I use Codeigniter 3.1.11 and have a question. I need to update about 1 million (in future will be much more) rows in MySQL table every 1 hour by Cron. But problem is if I update more than about 200-300 rows using this code, my server CPU is 100% loaded and table is stopping update after about 200-300 rows. And I even have to restart the PHP on my server to get the server back to normal. What I do wrong? Please, suggest a solution how to do this task correctly, so that the query to the database is executed quickly and there is no heavy load on the server.
This is the code from controller:
This is the code from model:
This is the code from controller:
PHP Code:
function cron_rows_update() {
$this->members_model->rows_update();
}
This is the code from model:
PHP Code:
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();
if($query->num_rows() > 0) {
foreach($query->result_array() as $row)
{
$game_total_balance = round($row['game_vault_balance'] + $row['game_available_balance'], $currency_numbers_after_dot);
// Game Rating Calculate
// Rating Part1
// Rating Part1_1
if ($row['game_vault_balance'] == '0') {
$rating_part1_1 = '0';
}
if ($row['game_vault_balance'] > '0' AND $row['game_vault_balance'] < '20') {
$rating_part1_1 = '0.1';
}
if ($row['game_vault_balance'] > '20' AND $row['game_vault_balance'] < '2000') {
$max_game_vault_balance = '2000';
$percent = floor($row['game_vault_balance'] * 100 / $max_game_vault_balance);
$additional_rating = '0.05' * $percent / 100;
$rating_part1_1 = round('0.1' + $additional_rating, 2);
}
if ($row['game_vault_balance'] >= '2000') {
$rating_part1_1 = '0.15';
}
// Rating Part1_2
if ($game_total_balance == '0') {
$PER_part1_2 = '0';
}
if ($game_total_balance > '0' AND $game_total_balance < '20') {
$rating_part1_2 = '0.1';
}
if ($game_total_balance > '20' AND $game_total_balance < '2000') {
$max_game_total_balance = '2000';
$percent = floor($game_total_balance * 100 / $max_game_total_balance);
$additional_rating = '0.05' * $percent / 100;
$rating_part1_2 = round('0.1' + $additional_rating, 2);
}
if ($game_total_balance >= '2000') {
$rating_part1_2 = '0.15';
}
// Rating part1_3
$rating_part1_3 = '0';
// Rating part1_4
$PER_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;
}
if ($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;
// Update row in members table
if ($game_total_balance > '0') {
$this->db->where("UserID", $row['UserID']);
$this->db->set("game_vault_balance", "game_vault_balance - " . $amount_from_game_vault_in_hour, FALSE);
$this->db->set("game_available_balance", "game_available_balance + " . $game_balance_in_hour_amount, FALSE);
$this->db->set("rating", $rating_member, FALSE);
$this->db->set("game_rating_and_balance_update_last_time", 'NOW()', FALSE);
$this->db->update("members");
}
}
}
return;
}