>>>>> "wayne" == wayne strauss <wayne_strauss@stripped>
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
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.