Which is a faster way for checking for duplicate entries, then creating a new entry?

I want to check if an entry exist, if it does I'll increment it's count field by 1, if it doesn't I'll create a new entry and have it's count initialize to 1. Simple enough, right? It seems so, however, I've stumbled upon a lot of ways to do this and I'm not sure which way is the fastest.

1) I could use this to check for an existing entry, then depending, either update or create:

if(mysql_num_rows(mysql_query("SELECT userid FROM plus_signup WHERE userid = '$userid'")))

2)开发者_如何学Python Or should I use WHERE_EXISTS?

SELECT DISTINCT store_type FROM stores
WHERE EXISTS (SELECT * FROM cities_stores
WHERE cities_stores.store_type = stores.store_type);

3) Or use this to insert an entry, then if it exists, update it:

INSERT INTO table (a,b,c) VALUES (1,2,3)
   ON DUPLICATE KEY UPDATE c=c+1;
UPDATE table SET c=c+1 WHERE a=1;

4) Or perhaps I can set the id column as a unique key then just wait to see if there's a duplicate error on entry? Then I could update that entry instead.

I'll have around 1 million entries to search through, the primary key is currently a bigint. All I want to match when searching through the entries is just the bigint id field, no two entries have the same id at the moment and I'd like to keep it that way.

Edit: Oh shoot, I created this in the wrong section. I meant to put it into serverfault.

---

I believe it's 3.

Set an INDEX or a UNIQUE constraint and then use the syntax of number 3.

---

It depends which case will happen more often.

If it is more likely that the record does not exists I'd go for an INSERT IGNORE INTO, checking affected rows afterwards; if this is 0 the record already exists, so an UPDATE is issued.

Otherwise I'd go for INSERT INTO ... ON DUPLICATE KEY UPDATE.