List:General Discussion« Previous MessageNext Message »
From:Michael Widenius Date:January 10 2000 8:45pm
Subject:Re: is update more expensive than insert in Mysql?
View as plain text  
>>>>> "wayne" == wayne strauss <wayne_strauss@stripped>
> writes:

wayne> I disagree. An insert still has to look at the index to determine if the key is
wayne> already being used by another row (3 IO's right there, maybe logical, maybe
wayne> physical). If not, then inserting the row will still result in 2 or more  IO's
wayne> (index and data) which may result in physical IO and possibly a page split. An
wayne> update has do do 3 IO's to determine if the row is in the index then 1 or more
wayne> IO's to fetch the row but it may be possible to write the changes back out to
wayne> the same physical area so there might be no need to delete and add the same key
wayne> just to update. IMHO there's no easy answer, it depends on the schema/data.

Some more information:

When doing an INSERT MySQL first finds the place where to insert the
key. At this point, in case of UNIQUE/PRIMARY keys,  it checks if
there is a duplicated key nearby (in other words, you will normaly
loose only a simple compare in case of UNIQUE keys).  If not, it will
then insert the key and split pages if necessary.

On UPDATE the row must first be fetched, then one updates this and
then all changed indexes has to be deleted + rewritten. After this the
new row is written on top of the old row.

In other words; If you have many indexes that you don't change, then
an UPDATE may be faster than an insert as you in this case only have
to update the changed indexes and the row.

wayne> Patrick Greenwell <patrick@stripped> on 01/06/2000 05:29:40 PM

wayne> To:   Cheng-Wei Cheng <cheng@stripped>
wayne> cc:   mysql mailing list <mysql@stripped> (bcc: wayne
> strauss/Medscape)

wayne> Subject:  Re: is update more expensive than insert in Mysql?


wayne> On Thu, 6 Jan 2000, Cheng-Wei Cheng wrote:

>> any comments?

wayne> Yes. Consider the process: Update has to find the particular row to be
wayne> updated before it can perform the operations. A simple insert does not
wayne> necessitate doing so.

This is correct in the case that one changes all keys for the row.

Regards,
Monty
Thread
shutdown mysql when backing up?hypnos6 Jan
  • Re: shutdown mysql when backing up?Tim Bunce6 Jan
    • Re: shutdown mysql when backing up?Paul DuBois6 Jan
      • Re: shutdown mysql when backing up?Tim Bunce6 Jan
        • Re: shutdown mysql when backing up?Tim Bunce6 Jan
          • RE: shutdown mysql when backing up?Daniel Lafraia7 Jan
            • Re: shutdown mysql when backing up?Tim Bunce7 Jan
        • Re: shutdown mysql when backing up?Michael Widenius7 Jan
          • is update more expensive than insert in Mysql?Cheng-Wei Cheng7 Jan
            • Re: is update more expensive than insert in Mysql?Patrick Greenwell7 Jan
            • Re: is update more expensive than insert in Mysql?Mark Zieg7 Jan
          • Re: shutdown mysql when backing up?Tim Bunce7 Jan
            • Re: shutdown mysql when backing up?Michael Widenius7 Jan
  • Re: shutdown mysql when backing up?Scott Hess6 Jan
    • Re: shutdown mysql when backing up?hypnos7 Jan
Re: is update more expensive than insert in Mysql?wayne_strauss7 Jan
  • Re: is update more expensive than insert in Mysql?Kyle Hayes7 Jan
  • Re: is update more expensive than insert in Mysql?Michael Widenius10 Jan
    • Re: is update more expensive than insert in Mysql?Eric Peters11 Jan
      • Re: is update more expensive than insert in Mysql?Benjamin Pflugmann11 Jan
      • Re: is update more expensive than insert in Mysql?Michael Widenius11 Jan
Re: shutdown mysql when backing up?Fred Lindberg7 Jan