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]
