MYSQL update
What's the best way to update a few fields in a table when some may have changed and some not?
I can use PHP with if/else, but of course I would need to have a full query in each clause to cover them. With three vars, that's 4 if/elses.
<?php
if $total changed && $average changed && $max changed
elseif
$total changed && $average changed
elseif
$average changed && $max changed
elseif
$total changed && $max changed
?>I know mysql 5.x it doesn't update a column if the value it currently has is the same. But currently I'm on 4.x (yeah I know).
Is it ok to just update all values regardless of if they are changed or not?
teammatt3 posted this at 21:02 — 19th March 2009.
He has: 2,102 posts
Joined: Sep 2003
Yes.
You shouldn't be storing aggregates like averages, totals and maxes in the database (except in certain cases). They should be calculated on the fly with a query. What's with the total changes, average changed, max changed stuff?
greg posted this at 21:07 — 19th March 2009.
He has: 1,581 posts
Joined: Nov 2005
She can be a bit quiet at times about her being annoyed, and stabs you in the back when not looking.
The three in question are $album_name, $description and $is_live.
(is_live determines if content viewable by public or only admins)
pr0gr4mm3r posted this at 21:14 — 19th March 2009.
He has: 1,502 posts
Joined: Sep 2006
Why? Storing an extra field is way more efficient than calculating something over and over again.
teammatt3 posted this at 22:05 — 19th March 2009.
He has: 2,102 posts
Joined: Sep 2003
Storing the aggregate makes reads faster, no doubt about that. But it has other costs. You have to update the aggregate column for every regular update, delete and insert. That complicates your database schema, and your application code.
In the scenario where reads are more common, you could use your database's query cache to avoid the redundant calculation. Then you don't have to denormalize your database schema or complicate your application code.
But in the case where you don't have a query cache and you are performing many reads, you can still calculate the columns on the fly without a huge cost (depending on the size of your tables). But if you have large, growing tables, you may opt to store the aggregate in the table.
I would always avoid storing duplicate data in my database until there is a noticeable performance hit. I don't have a problem with denormalization, but I don't think you should start off that way.
EDIT: If you're using COUNT() and MyISAM, that table's row count is stored in the storage engine automatically, so there is no overhead. I think that's the only aggregate that is stored automatically.
greg posted this at 21:47 — 19th March 2009.
He has: 1,581 posts
Joined: Nov 2005
Maybe the two totals that give the average are not to be stored, so then you have only one field versus two (calculation required in either instance).
If you are inserting data into the same row at the same time as the average, and also retrieving other data from the same row when getting the average (as is usually the case) then it's six and two threes.
Calculate and store average and two totals and retrieve all - VERSUS - store two totals and retrieve and calculate average.
So not storing the average saves a field, but calculation is required on retrieval, and when retrieving there may be a lot more scripts going on than when you would have been inserting it.
Which all boils down to one thing...
As with any database requirement, PHP requirement and best practice between the two, it COMPLETELY depends on the usage and situation.
And most situations are different to each other.
Want to join the discussion? Create an account or log in if you already have one. Joining is fast, free and painless! We’ll even whisk you back here when you’ve finished.