> 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.
Gotcha. I wasn't sure if you'd checked the manual or just run a huge number of EXPLAINs on your queries. > I was hoping someone with some experience might offer something more > than pointing a finger to some documentation. Well, I can't offer an exact answer, but perhaps a tip. Have you considered adding a pair of DATE fields to your tables to determine when the records were added and subsequently modified? You could use those fields in conjunction with INSERT, UPDATE and IGNORE to get the desired effect. While this doesn't directly address your question, it may be a better long-term solution to your problem. If you continue going about things with your current technique, you will likely run into the same problem once your table grows, even with the best choice of UPDATE or INSERT. Edward Dudlik Becoming Digital www.becomingdigital.com ----- Original Message ----- From: "Shane Bryldt" <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]> Sent: Sunday, 15 June, 2003 06:03 Subject: Re: Question about INSERT vs UPDATE 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] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]