Updating a row in mysql
Here’s an example that might not be so obvious: Let’s say you want to make a page view counter for your shop, but you want to implement caching for your pages and running an SQL UPDATE query for each page is out of the question.
An efficient solution would be to make a logfile with each view as a new line appended in a file.
So here’s the final CASE version: Although it doesn’t beat the speed of normal queries run in a row, it can still get close enough.
Also, since many people asked why bother that much when you can just run the normal queries, you can consider this an experiment to find alternatives.
Also, as a final note, you should always run an OPTIMIZE TABLE query after all these updates.
If you already created your My SQL database, and you decide after the fact that one of the columns is named incorrectly, you don't need to remove it and add a replacement; you can simply rename it.
You can just create a long query to update the database and run it only once instead of hundreds of small queries (which in case you didn’t figure it out, would bring your database to its knees in many cases).Also, keep in mind that in order to gain advantage of My SQL optimizations all those UPDATE queries need to be run in a batch.Running each UPDATE query when the page is viewed for example is not the same thing (and that’s pretty much the problem I wanted to solve as efficiently as possible).After a bit of research I figured out My ISAM doesn’t support transactions (yes, silly me), so the tests above were done using simple consequent queries (modified up there as well).
However, it seems My SQL does some internal optimizations and runs them very efficiently.
No, he's not an up-and-coming political powerhouse, or a can't-lose campaign manager.