MySQL function to insert record if update fails?

Question

Part of the system I'm working on at the moment involves a log in mysql, with counts being frequently updated.

The data being inserted is of the format:

   date    | name | count |
-----------+------+-------+
2009-01-12 | alan |   5   |
2009-01-12 | dave |   2   |
2009-01-12 | mary |   1   |

This data is parsed regularly from a flat file, summarised as above in preparation for a db insert/update - the unique key on the database is the (date, name) pair.

Previously, this system would check the existing table for any records for a given date and name pair before deciding on an update or insert.

The problem we're having is, as this table grows, the response time isn't getting any better, and we want to reduce the number of queries as much as possible.

The system was recently updated to run a INSERT ... ON DUPLICATE KEY UPDATE query, which has reduced the number of selects marginally, but our common case by some distance is the update.

I'm wondering if anyone knows of a mysql function that's essentially INSERT ... ON DUPLICATE KEY UPDATE in reverse, i.e. will try to update a row, if none match then perform the insert?

---

Edit

I didn't make it too clear above, what I would like to do when I have the record ('2009-01-12','alan','5') for example, is:

UPDATE table SET count = count+5 WHERE date = '2009-01-12' and name = 'alan';

and if the above fails, insert the above data. The need to increment a counter is why REPLACE won't work. Replace performs a delete & insert, and doesn't let you refer to the row being deleted, so count = count + 5 wouldn't increment the previous count value for by 5.

@jasoncohen - the INSERT ... ON DUPLICATE KEY UPDATE does do the job, but I'm asking if there's a more optimal way to do this.

Sorry for any confusion resulting from the poor original phrasing!

Answer 1

Why isn't the INSERT sufficient? Even if most of the time it's a duplicate key and therefore an update (instead of the other way around), it's still the correct operation right?

Are you just asking for performance concerns?

Answer 2

Use the REPLACE function:

http://dev.mysql.com/doc/refman/5.0/en/replace.html

Answer 3

It's just the same. With "UPDATE ... ON NO KEY INSERT", the database engine will still have to check first if there is something to update. Hence no need for a separate construct even if update is most common

Answer 4

I have been trying to figure out what exatcly it is that you want, and as I see it, you don't want to do anything if the data match? I dont see a solution to that, if the "count" somehow will change and need updating, you're stuck with the INSERT INTO ON DUPLICATE KEY UPDATE (which I don't really see the issue with).

However, if the count never will be updated, you might wanna look into INSERT IGNORE INTO which will ignore the insert if the unique key (date + name) already exists.

You haven't considered "flushing/rotating" your flat file therefor only checking for added material? Or isn't that possible?

Edit:

The INSERT will fail instantly because of the duplicate key violation, and trigger the UPDATE in that case. Shouldn't be any performance concern at all. I do this all the time on pretty large database, and I haven't noticed any huge performance difference when starting from a empty database as opposed to an already populated database.

However, it's probably a good thing to run ANALYZE TABLE/OPTIMIZE TABLE from time to time in order to keep the index in good shape.