What's the fastest way to add or update unique values to a MYSQL database?

Question

I want to track visitors to a page on my website by their IP addresses and manage the records in a MYSQL database. I have a primary key field called 'ip' and a second field called 'num_visits'

As people visit the page, the IP is either recorded as new or updated.

I can think of two ways to do it so far (the following queries are abbreviated):

1.) SELECT ip. If rows are round, run an UPDATE and increment num_visits where ip = ip. If no rows are round, run INSERT.

2.) just run INSERT from the start, assuming it's a new IP. If it fails because it already exists, then I know to do an UPDATE instead.

My database is expected to get large (30,000 entries or more), so I wonder if #1 or #2 is faster or more efficient.

I assume that an update or insert involving a unique field automatically performs some sort of select query to make sure the new insert is actually unique.

Answer 1

I would go with option 3:



Run an update first. If that fails (0 rows affected), then run an insert.

Why? You would only have to make 2 calls (update, insert) the first time someone visits. Once the IP is in the table, there will only be 1 call.

Your option 1 always requires 2 calls: select then insert or update.

Your option 2 requires 1 call on the first visit, but 2 calls on every subsequent visit.



Option 2 would be better than 3 only if a very high percentage of your visitors only hit the site once. (But if that were the case, there probably isn't a huge advantage to tracking IP's in the first place).

Answer 2

I'm not sure which is more efficient, they are probably about the same. I would use technique 1. 30K entries is pretty light for a mysql server so either method should be fast.

Answer 3

you could use an Insert with ON DUPLICATE UPDATE. this will cause new information to be placed if the Primary is already there.