1 query and 1 conditional query OR always 2 queries

Let’s suppose the following.

if ($conditionVar > 0) {
$update=$dbc->query("UPDATE myTable SET column=1 WHERE id=1");
if ($conditionVar==2) {
$update=$dbc->query("UPDATE myTable SET column=2 WHERE id=2");
} 
}

The code1 above works same as the code2 below.

if ($conditionVar > 0) {
$update=$dbc->query("UPDATE myTable SET column=1 WHERE id=1");
$update=$dbc->query("UPDATE myTable SET column=2 WHERE id=2");
}

when $conditionVar is 1,
The code1 performs 1 query and read 1 condition inside conditionVar > 0.
The code2 performs 2 queries inside conditionVar > 0.

when $condition is 2,
The code1 performs 2 queries and read 1 condition inside conditionVar > 0.
The code2 performs 2 queries inside conditionVar > 0.

In total,
The code1 performs 1 query and read 1 condition or performs 2 queries and read 1 condition.
The code2 always performs 2 queries and read 1 condition.

If the possibility of $conditionVar=1 and $conditionVar=2 are same,
Which code is better in perfomance in your instinct?

In my opinions,
I guess that code1 is better in perfomance.
And I think, I am afraid to say, code2 is better in readibility.

Answer: Neither, IMO.
If you’re expressly pulling these values from the database to check them, then you’re wasting a query.
If id=1 must have column=1, and id=2 must have column=2, set them regardless.

IE:
“SELECT column FROM mytable WHERE id = 1 OR id = 2”
…do some code to figure out it they’re right…
“UPDATE column”
“UPDATE column”

Is a waste of two queries, probably. (The SELECT and one of the UPDATE’s)

“UPDATE mytable SET column = id WHERE id IN(1,2)”
(Note: this only works if specifically referencing the column being the same as the id. If that’s not to be the case, you’d have to modify the query to include a CASE statement.)

Because we don’t really care if the rows already have the right values, we’re telling them what their correct values are.

1 Like