Re: Relative efficiency (in terms of disk io) between REPLACE and UPDATE w/InnoDB
Ahhh, thats very good to know. Thank you. On Wed, 2004-12-15 at 19:09 -0500, Harrison Fisk wrote: No. In InnoDB an UPDATE is done as a DELETE/INSERT internally because it is multiversioning and it has to be able to rollback in case of a problem. So the UPDATE effectively does the same thing as the REPLACE that hits a problem. I would most likely stick with REPLACE since it is a bit easier to understand how it is working and has less client code. Regards, Harrison -- John A. McCaskey Software Development Engineer Klir Technologies, Inc. [EMAIL PROTECTED] 206.902.2027 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Relative efficiency (in terms of disk io) between REPLACE and UPDATE w/InnoDB
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? Is this correct? -- John A. McCaskey Software Development Engineer Klir Technologies, Inc. [EMAIL PROTECTED] 206.902.2027 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Relative efficiency (in terms of disk io) between REPLACE and UPDATE w/InnoDB
On Wed, 2004-12-15 at 11:46 -0600, gerald_clark wrote: 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. Thats true, and thanks for pointing it out. However, in my situation, I am not concerned with this. The tables in question are basically data logging tables, which are updated by a single process which will always serial UPDATE, check if any rows were matched (I'll use the CLIENT_FOUND_ROWS option when connecting), and then INSERT if not. Furthermore, the window for errors could be closed in any situation by fist UPDATE, check if any rows were matched, then REPLACE (rather than insert). This would make the situation where a row did not already exist actually more expensive than before, but because that situation occurs only about 1/24 times for me, it is still much faster overall adding the UPDATE I think. This is assuming that as I stated above the REPLACE query works as described and is truly just a DELETE/INSERT and is not optimized itself to avoid some of the overhead when a row already exists. Is this correct? -- John A. McCaskey Software Development Engineer Klir Technologies, Inc. [EMAIL PROTECTED] 206.902.2027 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Relative efficiency (in terms of disk io) between REPLACE and UPDATE w/InnoDB
Hi, On Wednesday, December 15, 2004, at 12:51 PM, John McCaskey wrote: On Wed, 2004-12-15 at 11:46 -0600, gerald_clark wrote: 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. Thats true, and thanks for pointing it out. However, in my situation, I am not concerned with this. The tables in question are basically data logging tables, which are updated by a single process which will always serial UPDATE, check if any rows were matched (I'll use the CLIENT_FOUND_ROWS option when connecting), and then INSERT if not. Furthermore, the window for errors could be closed in any situation by fist UPDATE, check if any rows were matched, then REPLACE (rather than insert). This would make the situation where a row did not already exist actually more expensive than before, but because that situation occurs only about 1/24 times for me, it is still much faster overall adding the UPDATE I think. This is assuming that as I stated above the REPLACE query works as described and is truly just a DELETE/INSERT and is not optimized itself to avoid some of the overhead when a row already exists. Is this correct? No. In InnoDB an UPDATE is done as a DELETE/INSERT internally because it is multiversioning and it has to be able to rollback in case of a problem. So the UPDATE effectively does the same thing as the REPLACE that hits a problem. I would most likely stick with REPLACE since it is a bit easier to understand how it is working and has less client code. Regards, Harrison -- Harrison C. Fisk, Trainer and Consultant MySQL AB, www.mysql.com Mobile: +1 315 380-6048 Are you MySQL certified? www.mysql.com/certification -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Relative efficiency (in terms of disk io) between REPLACE and UPDATE w/InnoDB
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]
