0

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) 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.

Vadoff
  • 9,219
  • 6
  • 43
  • 39
  • could you use an auto-incrementing field? –  Jul 30 '11 at 11:00
  • @Vadoff: It should not go to serverfault, it would fit better on [programmers](http://programmers.stackexchange.com/), but no worries. It is answerable even here. – Shef Jul 30 '11 at 11:04
  • @oracle certified professional: No I can't, I don't want it to auto-increment. – Vadoff Jul 30 '11 at 11:08

2 Answers2

2

I believe it's 3.

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

Shef
  • 44,808
  • 15
  • 79
  • 90
0

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.

wonk0
  • 13,402
  • 1
  • 21
  • 15
  • `INSERT IGNORE` brings much more trouble to the table than it solves. – Shef Jul 30 '11 at 11:45
  • could you please explain this? – wonk0 Jul 30 '11 at 12:26
  • `INSERT IGNORE` will not issue an error, if the query didn't work because of many other reasons, but _not_ because there was a duplicate, then you won't know about the outcome. – Shef Jul 30 '11 at 12:51
  • of couse it does not throw an error on a duplicate, that's the point of ignore. and that's the reason I mentioned you have to check for affected rows – wonk0 Jul 30 '11 at 13:23
  • Re-read my comment. I will rephrase it for you if it's not clear, yet. – Shef Jul 30 '11 at 13:26
  • INSERT IGNORE will issue *any* error a "normal" INSERT also does; it will simply not raise an error on duplicate keys. This is my knowledge of INSERT IGNORE. Should you know of any error which will not be reported please let me know. – wonk0 Jul 30 '11 at 16:45
  • Read the accepted answer on [“INSERT IGNORE” vs “INSERT … ON DUPLICATE KEY UPDATE”](http://stackoverflow.com/questions/548541/insert-ignore-vs-insert-on-duplicate-key-update) to see what errors will be bypassed and what are the disadvantages of `INSERT IGNORE`. – Shef Jul 30 '11 at 17:02