An update that does not find a qualifying row to update executes in the same time that a select would. The only penalty is the time needed to verify that it does not exist.
Your Friendly Neighborhood DBA, Chuck -----Original Message----- From: Ian Harisay [mailto:[EMAIL PROTECTED] Sent: Friday, March 30, 2007 11:56 AM To: [email protected] Subject: RE: Updating counts I don't know if that would be speedy. Updates are far more expensive than an insert. It does sound portable and safe though. -----Original Message----- From: Tim Bunce [mailto:[EMAIL PROTECTED] Sent: Friday, March 30, 2007 9:47 AM To: Ronald Kimball Cc: George Bills; [email protected] Subject: Re: Updating counts On Fri, Mar 30, 2007 at 09:55:23AM -0400, Ronald Kimball wrote: > Garrett, Philip (MAN-Corporate) wrote: > > > > George Bills wrote: > > > I've also seen MySQL syntax like "ON DUPLICATE KEY UPDATE ..." > > > which looks good, but I'd like to avoid an extra dependency on a > > > database that has to be set up and maintained (hence SQLite). > > > > > > My preference for a solution would be something that's simple and > > > maintainable primarily, followed by fast (for reads first, writes > > > second) - but I don't have the database experience to know what > > > the best solution is. Is there an easy way? > > > > Yes, but it looks like you've already done it: try fetch, update if > > successful, insert otherwise. > > This will work as long as you don't have to worry about concurrent > processes inserting rows into the table. If you do have that, then > you've created a race condition. Two processes could try fetch at > roughly the same time, fail, and insert the same row, causing one of the processes to get a duplicate key error. > > Instead you could insert while trapping errors, and update if a > duplicate key error occurs. However, that probably requires checking > for a database-specific error code or message, at which point you > might as well just take advantage of MySQL's ON DUPLICATE KEY UPDATE. Instead of "insert while trapping errors, and update if a duplicate key" you can "update then insert if the update affected 0 rows". That avoids the need to check for specific error codes. It also optimises for the common case of the row already existing. If the update affected 0 rows then the following insert may fail if some other process inserts a matching record before you get to do your insert. If your insert fails you can just retry the update again before aborting. Portable, safe, speedy. Tim.
