GatesPlan GatesPlan - 14 days ago 9
MySQL Question

Best practice for update with providing various number of field

I'm trying to figure out what is the best way to update mySQL table.

Following is what I'm doing now.
Lets suppose the table consisted with 4 field (colA, colB, colC, colD).
There is an

Element
object. The object will contain value of each column.
Element
class have some special property to indicate what property were edited using bitwise operator.

class Element {
const FLAG_EDIT_A = 1;
const FLAG_EDIT_B = 2;
const FLAG_EDIT_C = 4;
const FLAG_EDIT_D = 8;

public colA, colB, colC, colD;
protected $editedWhat = 0;

public function __set($varName, $value) {
$this->$varName = $value;
if($varName === 'colA') {
// If I change colA, FLAG_EDIT_A will be added to $editedWhat.
// However, if I edit colA twice, FLAG_EDIT_A must NOT be
$this->editedWhat += ($this->editedWhat & self::FLAG_EDIT_A)? 0 : self::FLAG_EDIT_A;
}
..
}
}


The element will be sent to PDO Adaptor class
ElementAdaptor
.

interface ElementAdaptor {
public function save(Element $element);
}


ElementAdaptor::save() will read
$element->editedWhat
property and dynamically create SQL fragment.

public function save(Element $element) {
// ..
if($element->editedWhat & Concept::FLAG_EDIT_A) {
$SQL_Array[] = 'colA=:A';
};
// ..etc..

if(count($SQL_Array) > 0) {
$SQL .= implode(', ', $SQL_Array).' ';
} else {
return false;
};
// ..etc..

// Declare prepared statement.
if($element->editedWhat & Concept::FLAG_EDIT_A) {
$stmt->bindValue(':A', $concept->colA);
};
}


Frankly I didn't test this code but I know that it will work after few debugging.

What I'm wonder is, is that a good way to update a field dynamically according to what has been changed? I don't have enough confidence for my code because I learned this from internet.. although long practice over few years and good examples, cannot believe myself because its done with no one's correction. If there is other way to achieve same result, please help.

Answer

Good enough for conserving memory and PHP execution speed. Depending on the circumstances (heavy load database activity or traffic) sometimes (often?) it may be better to keep a copy of the original values and decide what has been modified by comparing the old/original value with the old one.

Rationale: most of the time, it is easier to create a cluster of Web servers than a cluster of a database servers (replication, consistency, etc at the database level is hard. A web server rarely needs to be that level of persistence/consistency as a database, especially with the RESTfull/stateless services).

This is to say: the database is more likely to act as a bottleneck than a Web server, therefore "paying Web Server resources" to protect a db server makes sense more often than not.

[Edited]: for example, you have a many WebServers using the same DB-server, maybe the same database. Since the DB-server is potentially a bottleneck, you want to protect it as much as possible (number of transactions, bandwidth to the DB host if not hosted by the same computer as the WebServer).

In such a situation, you sacrifice computing power/memory on the WebServers to detect the absolute minimal changes that need to be saved before sending the request to the server:

  • a "dirty field bitmask" scheme will only detect if a field has been touched at least once, but if I'm setting the field to the same value immediately or after a sequence of ops (say original A=2, then I set A=3 and revert to A=2), you will ask the DB server for an update on that field even if it doesn't need to be.

  • "comparison with an original copy" scheme will sacrifice resources on the WebServer: memory (to keep the original) and CPU power (to perform the comparison just before saving). But it will absolutely protect the DB-server against unneeded updates.

Also, minor nitpick:

        // If I change colA, FLAG_EDIT_A will be added to $editedWhat.
        // However, if I edit colA twice, FLAG_EDIT_A must NOT be 
        // $this->editedWhat += ($this->editedWhat & self::FLAG_EDIT_A)? 0 : self::FLAG_EDIT_A;

        // Isn't this simpler?
        $this->editedWhat = $this->editedWhat | self::FLAG_EDIT_A;

See bitwise ops