List:General Discussion« Previous MessageNext Message »
From:Michael Widenius Date:January 10 2000 11:04pm
Subject:Re: is update more expensive than insert in Mysql?
View as plain text  
<cut>
>> 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.
>> 

Eric> Could you (or someone) explain "If you have many indexes that you don't change,"
Eric> from an implementation standpoint - like what would the table look like? or etc

Eric> This message just happend to catch my eye so I havn't really back tracked the
Eric> thread to see the references - but It sounds like something I made a decision
Eric> to go with an INSERT over an UPDATE for, but could possibly be ignorant?

Eric> Thanks, I appreciate your time

CREATE TABLE test (a int, b int, c int, d int, e int,
                   key(a,b,c,d), key(b,c,d), key (c,d), key(d));

# Note that e is not a key.

The followign updates will be faster than an insert as no keys changes:

UPDATE test set e=5 where a=1;
UPDATE test set e=5 where a=1 and b=2 and c=3 and d=4;

The following will be slower than an insert as we need to do 1 row
read, 4 key deletes, 4 key inserts and one row write.

UPDATE test set a=5,b=5,c=5,d=5 where a=1 and b=2 and c=3 and d=4;

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