Spending time optimizing an sql query VS Pushing unnecessary data from database

Question

I have a considerably big table in my database, and I want to have a function that, for example, performs an UPDATE query.

What I used to do in my older projects was passing all the values for all the columns, and insert them in the query string, like this example:

function updateUser($id, $name, $username){

    $query =  "UPDATE user SET name = '{$name}', username = '{$username}' WHERE id = '{$id}' ";

    return mysqli_query($this->conn, $query);

}

That meaning, every column was being altered even those that weren't changed.

But, this time being a big table, I don't want to sacrifice the application speed.

What I'm trying to do is make some comparisons, therefore optimizing the query, and only then sending it to the database.

Staying in the UPDATE query example from before, this is kind of what I want to do:

function updateUser($old_user, $new_user, $user_id){

    $changed = false;

    $oldFirstName = $old_user->getFirstName();
    $newFirstName = $new_user->getFirstName();
    if($oldFirstName == $newFirstName){
        $firstNameQuery = "";
    }else{
        $firstNameQuery = " first_name = '".mysqli_escape_string($this->conn, $newFirstName)."',";
        $changed = true;
    }

    $oldLastName = $old_user->getLastName();
    $newLastName = $new_user->getLastName();
    if($oldLastName == $newLastName){
        $lastNameQuery = "";
    }else{
        $lastNameQuery = " last_name = '".mysqli_escape_string($this->conn, $newLastName)."',";
        $changed = true;
    }

    $oldEmail = $old_user->getEmail();
    $newEmail = $new_user->getEmail();
    if($oldEmail == $newEmail){
        $emailQuery = "";
    }else{
        $emailQuery = " email = '".mysqli_escape_string($this->conn, $newEmail)."',";
        $changed = true;
    }

    if($changed){
        $query = "UPDATE user SET {$firstNameQuery}{$lastNameQuery}{$emailQuery} WHERE user_id = {$user_id}";
        return mysqli_query($this->conn, $query);
    }else{
        return 0;
    }
}

Although, as you can see, as the table grows this function gets bigger and with a lot more comparisons and attributions.

My question is: Am I saving a noticeable amount of time doing this, or it isn't whorth it?

DB don't care about columns, row are what matter. BTW, take a look at [How can I prevent SQL injection in PHP?](http://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php) as your code is not safe — Blag, Dec 04 '16 at 00:16
Oh I see. Thanks. On the security issue, I thought `mysqli_escape_string() `took care of SQL injections, don't they? — Matheus Correia, Dec 04 '16 at 00:21
when you read one row, you get all the col and it's easy for the DB as the data are usually close/together on the drive. Bad think happen when you have to read a non indexed col of every row, as the DB'll have to read and jump in the whole db file on the drive. for the Injection, I didn't seen your `mysqli_escape_string()` , so yes it's safe, but go with prepared statement if you can, it's more error proof as you put all the security on one line and will just have to keep in mind "never put a `$`var in my query" — Blag, Dec 04 '16 at 00:30

score 1 · Accepted Answer · answered Dec 04 '16 at 00:11

1

You are probably making the code less efficient. Much of the time for an update is on logging the transaction and physically storing the data page for each record. Because all the columns for a single record are (typically) stored on a single page, updating one column or many columns doesn't matter.

On the other hand, the additional comparisons in the application also take time.

Of course -- as with any performance related issue -- you can test the different scenarios. I wouldn't expect any noticeable improvement in performance by going through such logic to reduce the number of columns in the update, unless it eliminated entirely the need for updating certain rows.

answered Dec 04 '16 at 00:11

Gordon Linoff

1,242,037
58
646
786

I see. But on other cases, like selects, or inserts, does the same logic apply? – Matheus Correia Dec 04 '16 at 00:14
1

@MatheusAlmeida a row read or write is on the full row, if you care about perf, take a look on how to use INDEX on field you use in `WHERE`, and avoid storing big data (like raw picture) in the db, or at least keep them on a dedicated table you only query when needed – Blag Dec 04 '16 at 00:20

Spending time optimizing an sql query VS Pushing unnecessary data from database

1 Answers1