MySQL incrementing value vs inserting

Question

I'm tracking a specific value in a database, let's say it's number of bats in the world.

Is it preferred to have thousands of people updating this one value (click button to increment INT field with an UPDATE counters SET count = count + 1 WHERE animal = 'bat' and then SELECT the row) or insert a new record (INSERT and then SELECT COUNT(id))?

You can't have historical data (daily, hourly, etc) if you just have one number. Also if it's screwed up you are left with nothing, you can't fix it... — sashkello
– sashkello, Commented Jun 7, 2013 at 4:16
If everyone has their own bat then its better to INSERT each bat than to UPDATE just the number of bats. End of the day how would you know which bat belongs where? or if the number of bats are legitimately added etc — Hanky Panky
– Hanky Panky, Commented Jun 7, 2013 at 4:17
Is there a performance hit for either? E.g. would locking cause any UPDATE freeze-ups on the db server? — davidosomething
– davidosomething, Commented Jun 7, 2013 at 4:19
depending on the complexitiy of the query and the traffic on the site I doubt the updates would lock up unless you are running them in a transaction — JanR
– JanR, Commented Jun 7, 2013 at 4:20

JanR · Accepted Answer · 2013-06-07 04:18:55Z

1

I guess it depends on what you are trying to achieve. Do you want to be able to track which user has clicked to increment the number?

Also if you have thousands of users updating the same value you need to make sure that you are handling events in the case where 2 users click at the same time.

I reckon it depends on what you are trying to achieve in the end, if you want to track and report on the user's behaviors as well as not having to deal with multiple simultaneous clicks I would suggest you go for a record per user.

If the size of the database would be an issue some how you would probably have to write some queue-ing logic to ensure that all users' clicks get registered and then you could use the same value.

That being said, I would strongly suggest to use a record per click as it's easier to handle as well as provides roll back capabilities

answered Jun 7, 2013 at 4:18

JanR

6,1303 gold badges25 silver badges30 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

davidosomething Over a year ago

When 2 users click at the same time I'm guessing they will just be deadlocked until the db finishes with whoever clicked first. I think that's how it works at least...

JanR Over a year ago

Yes but it depends on how you get the last value, say the last value is determined when the first user clicks, then the second user clicks and gets the same value. Now the updates run and both update to the same number (the last value + 1). Which causes you to miss out on a click

davidosomething Over a year ago

it's done in the query - UPDATE counters SET count = count + 1 WHERE animal = 'bat'

JanR Over a year ago

But as was said in the comments above, you will not be able to verify validity of the clicks, nor will you be able to retrieve the number if for whatever reason it gets lost or altered incorrectly

Collectives™ on Stack Overflow

MySQL incrementing value vs inserting

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related