If this is your first visit, be sure to
check out the FAQ by clicking the
link above. You may have to register
before you can post: click the register link above to proceed. To start viewing messages,
select the forum that you want to visit from the selection below.

Enjoy an ad free experience by logging in. Not a member yet? Register.

Native Integer Incrementing (Existing Value)

Hi guys, I have a rather simple issue. I have a SMALLINT column, and I want this value to rise by one each-time I use that row (Basically this is recording the number of views on that row).
At the moment I am doing this (Stripped-down for the example):

There's nothing like that built-in to my knowledge. Your method is fine.

Not to mine either. Unlike sequences in oracle (which I'm pretty sure can be applied as I see fit, although I'd still see issues getting it to do it during a select), mysql doesn't have an auto-increment except for insertion id's on surrogate keys.
Pedant or Guelphdad may be able to verify; a custom procedure should let you get away with doing that. But then you need to execute via call, so I'm not sure if that would be more ideal or not.

Why do you thing executing via CALL would be a bad thing, FouLu? Most DB experts would tell you that you should do *ALL* work via Stored Procedures and not give the casual DB client permissions to use anything *except* Stored Procs!

An optimist sees the glass as half full.
A pessimist sees the glass as half empty.
A realist drinks it no matter how much there is.

Incidentally, the Stored Procedure will be roughly twice as efficient as making the two separate calls ( SELECT and then UPDATE ) as proposed in the first post.

That's because your PHP code only needs to make one interprocess invocation of MySQL. People don't realize how very expensive interprocess calls are.

The actual time spent in the DB is going to be roughly the same. The first query (whether it be the SELECT as first proposed or the UPDATE in my procedure) is going to ensure that the relevant record is fetched from disk to memory and so will be cached in memory for the second operation in both cases. So the disk-to-memory overhead will be the same and you are thus saving one interprocess call. Nothing to be sneezed at.

An optimist sees the glass as half full.
A pessimist sees the glass as half empty.
A realist drinks it no matter how much there is.

The SELECT would use the incremented value even if caching wasn't involved. SQL guarantees that operations will be done in the order requested.

The remark about caching has to do with the fact that, like most modern DB engines, MySQL makes use of a *HUGE* in-memory cache. Many many megabytes. (We actually had one system where we "tuned" MySQL to use 75% of available memory. Yes, you can do that. By default, though, it uses a lot less than that.)

So when you make a query of any kind on a given record, the disk *BLOCK* that contains that record is loaded into memory. And the DB engine kind of assumes that the most recent record fetches are going to be needed again, so it KEEPS that block in memory as long as it can (that is, it caches it).

In a typical MySQL installation, you can pretty much count on any record that was fetched till being in the cache many seconds or even a minute or two after it was fetched. And clearly if you do an UPDATE immediately followed by a SELECT as I do in the Procedure--or even if you do two separate queries, SELECT followed by UPDATE as in your first post--the record *WILL* still be in memory. So the second operation doesn't involve use of the disk at all. MySQL just remembers where it put that block in its memory cache and ZAM! It's all done in memory.

(Of course, after an UPDATE, eventually that disk block needs to be written back out to the disk, but MySQL does a lazy update, trying to schedule the writes when no other user-requested operations are taking place at a higher priority.)

An optimist sees the glass as half full.
A pessimist sees the glass as half empty.
A realist drinks it no matter how much there is.