As my post suggested, I have already addressed the tweaks this this
section of the manual addresses, and was hoping there might be some insight
on my original question, the process of INSERT vs UPDATE. That chapter was
helpful initially, but I have already addressed most of what that chapter
has to offer, however it still does not address my question. I have read
that chapter thoroughly.
I was hoping someone with some experience might offer something more
than pointing a finger to some documentation.
The documentation does not address UPDATE efficiency nor which operation
takes longer on a larger scale.
-Shane
----- Original Message -----
From: "Becoming Digital" <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>
Sent: Sunday, June 15, 2003 3:21 AM
Subject: Re: Question about INSERT vs UPDATE
> Consider going over Section 5.2.9 of the manual.
> http://www.mysql.com/doc/en/Insert_speed.html
>
> Edward Dudlik
> Becoming Digital
> www.becomingdigital.com
>
>
> ----- Original Message -----
> From: "Shane Bryldt" <[EMAIL PROTECTED]>
> To: <[EMAIL PROTECTED]>
> Sent: Sunday, 15 June, 2003 01:12
> Subject: Question about INSERT vs UPDATE
>
>
> Hello,
>
> I am new to the list, so pardon me if I am on the wrong one posting my
> question. If so, please direct me to the right place.
>
> My question is in regards to the efficiency of INSERT statements. I
have
> gone over some of the optimizations, and on a machine running the client
and
> MySQL 4 server, DELETEing a table and repopulating it with ~48k records
via
> INSERT, I have come to the conclusion that, regardless of hardware, the
delay
> will be too significant (on a test machine it ran about 25 seconds).
> What I am curious to know, is whether there is a significant increase
if I
> switch my method of saving from a complete memory dump, to a partial
memory
> dump. The overhaul involved would require a lot of code restructuring.
The
> situation is this, approximately 10k of those 48k records are actually
modified
> regularily. If I overhaul the code to indicate when a record needs to be
> updated, or inserted, and only call the appropriate action, is the
performance
> going to be significantly better?
> Keeping in mind it would have to search the 48k records to UPDATE the
10k
> modified records, as well as potentially INSERT new records (very few if
any).
> With 48k records, is updating 10k records faster than simply deleting
and
> reinserting every record?
>
> Alternatively, is there any way speeding up the INSERT time can be
achieved?
> First, I am using the default format, I believe is MyISAM. Second, I have
used
> table locking to optimize writing before the table is deleted and
repopulated,
> and unlocked after all records are inserted. Third, I am using multiple
INSERT
> lists (of 1000 records inserted at a time). Whether the lists is with
100, or
> 1000, I get about the same results of 25 seconds. I realize the hardware
I am
> using is not significantly powerful, but I think there is some
optimization I
> could make. Can you preallocate a definite number of rows for a large
insert
> operation?
>
> Any assistance would be appreciated, this project is flexible towards new
ideas
> to make the dumping more efficient. The alternative has been considered
to use
> a method of UPDATEing records immediately when changes are made in memory.
> Transaction overhead could become an issue however, with 10k+ records
actively
> changing at any given time. With an UPDATE method, dumping frequently may
> actually reduce the time required for the process, since it would update
fewer
> records.
>
> Anyone with some insight on this would be much appreciated if they could
offer
> some ways to speed up the process.
>
> Thanks,
> -Shane
>
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
>
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
