Codeigniter speed up multiple queries update?

Question

I have 200,000 rows to update. From tests using its method it takes 7 minutes to update 1,000 rows, so it will take around 24 hours to complete :(.

Is there a faster way to do this in Codeigniter than the method I'm using?

for($c=1;$c<=200;$c++) {
    $limit = 1000;
    $offset = ($c*1000)-1000;

    if($c==1) {
        $query = $this->db->query("SELECT email,password,first_name,last_name FROM users WHERE email!='' ORDER BY id ASC LIMIT ".$limit);
    } else {
        $query = $this->db->query("SELECT email,password,first_name,last_name FROM users WHERE email!='' ORDER BY id ASC LIMIT ".$offset.",".$limit);
    }

    $appdb->trans_start();
    foreach($query->result() as $row) {
        $email = $row->email;
        $password = $row->password;
        $first_name = $row->first_name;
        $last_name = $row->last_name;
        $newpassword = password_hash($password, PASSWORD_DEFAULT);

        $appdb->query("UPDATE users_user SET password='$newpassword',first_name='$first_name',last_name='$last_name' WHERE email='$email'");
    }
    $appdb->trans_complete();
}

The e-mail is unique, so I cannot use update_batch.

Have you indexed the field `email` in your database? If not it should speed up the query. — Michael Krikorev, Jun 06 '20 at 17:51

sauhardnc · Accepted Answer · 2020-06-06T20:08:47.497

I am no DB expert so, I'm not going to comment anything about it but one thing I do know is that

you can use update_batch() with one dynamic field (Reference) but if there are more than one fields, that's another story. By using update_batch() you'll escape multiple requests to the DB which should improve your performance slightly, if not significantly.

I've written a demo code below, comments are mentioned wherever necessary. See if it helps you.

for($i=1, $j=0; $i<=200; $i++){

    $limit  = 1000;
    $offset = ($i*1000)-1000;

    if($i == 1) {

        $query = $this->db->query("SELECT email,password,first_name,last_name FROM users WHERE email!='' ORDER BY id ASC LIMIT ".$limit);

    } else {

        $query = $this->db->query("SELECT email,password,first_name,last_name FROM users WHERE email!='' ORDER BY id ASC LIMIT ".$offset.",".$limit);

    }

    foreach($query->result() as $row){ // save data in the array

        $save[$j]['email']      = $row->email;
        $save[$j]['first_name'] = $row->first_name;
        $save[$j]['last_name']  = $row->last_name;
        $save[$j]['password']   = password_hash($row->$password, PASSWORD_DEFAULT);

        $j++; // increment array index
    }

    $appdb->trans_start();
    $appdb->update_batch('users_user', $save, 'email'); // dynamic email field
    $appdb->trans_complete();

    $save = array(); // empty the array after each iteration
}

For multiple dynamic fields you'll have to create your own update_batch() method. See here.

This solves my problem. Has reduced time tremendously, thanks. — user892134, Jun 06 '20 at 20:05

Codeigniter speed up multiple queries update?

1 Answers1