<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
