Re: Relative efficiency (in terms of disk io) between REPLACE and UPDATE w/InnoDB

[gerald_clark]

John McCaskey wrote:
I'm currently doing a large number of REPLACE queries, I know that these
evaluate as if doing a DELETE/INSERT pair, and I'm wondering if this is
true on a disk io level as well with extra io occuring for the delete,
and then re-insertion, vs what would occur with an UPDATE.
The way it works roughly each row gets updated around 12-24 times, the
updated do not affect the primary key, or any of the other keys for that
matter.  The table is INNODB.
So I'm thinking if the row is deleted then re-inserted, there is the
overhead of one finding the old row and marking it deleted, two
searching for the correct pos for the row in the table and in the key
structures, and three writing the row to disk.
Vs, and UPDATE would have the overhead of one finding the old row, and
two writing the updates to disk.
As such it seems like it actually be faster for me to attempt an UPDATE,
and if it fails, then do an INSERT, rather than using REPLACE?
This provides a window for errors.  You can attempt the update, and 
before the insert, another process
could insert the record.
And insert followed by an update on failure closes this window.

Is this correct?
 


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:    http://lists.mysql.com/[EMAIL PROTECTED]