Page 1 of 1

[SOLVED] Advantage of UPDATE vs. DELETE/INSERT?

Posted: Thu Feb 23, 2006 7:10 pm
by ryos
Hello,

In reading through the MySQL manual, I couldn't find a definite answer to this question, so I come to you.

I'm writing a directory program for internal use in a university department that keeps track of contact information, scheduling and experience, etc. for the teachers of the department. The department is small enough (around 50 teachers) that database size is not likely to be a concern for performance in this application.

The database has a "main" table that holds the contact info, and several "sub" tables to take care of scheduling. When I update these tables, I have a couple of options:

Option 1: DELETE/INSERT

This one is simpler to program. I just delete all rows that contain the teacherID (the reference to an entry in the main table), and insert new ones in their place. However, when I use this method, something twitches in the back of my head that tells me it's probably a Bad Thing to do. For one thing, the auto_increment keys will get larger and larger over time. Do they ever overflow, or are they smart enough to reset themselves?

Once again, it's not likely that the database will ever grow to the point where speed is an issue.

Option 2: UPDATE

This option requires me to SELECT the unique keys from every row containing a given teacherID, compare that list with the list from the form input to see if any are missing, DELETE those that are missing, INSERT those that are new, and UPDATE those that will stick around but whose values may have changed.

I don't have enough experience working with databases to know which method is better. From my lazy-sod-programmer point of view, Option 1 looks mighty nice, but it seems likely that Option 2 is a more Proper(tm) way to do things.

What do you experienced types think?

(I'm using MySLQ and PHP)

Posted: Thu Feb 23, 2006 9:06 pm
by feyd
Yes, option 2 is generally the "good" way.. but honestly, if you're more comfortable with option 1, go for it.

Posted: Thu Feb 23, 2006 9:20 pm
by Christopher
One issue with the standard MySQL table type is that is locks on writes. Because option 2 is writing less it will lock the table for a shorter time -- which would be better. If there is a lot of this activity going on then this might be a consideration.

Posted: Thu Feb 23, 2006 9:59 pm
by josh
Your auto_increments will keep growing but that is little concern, an int field in mysql can go up to 255 digits in length, with just 100 places you won't run into a problem until the 10 googelth update (large large number there) insertions take place. That means you can have

9999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999


insertions, I don't think that will be a problem..

Also updates only lock the row, not the whole table.. at least when using the myIsam storage engine

Posted: Thu Feb 23, 2006 10:06 pm
by Christopher
jshpro2 wrote:9999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999
Please keep the above Order Number for your records and include it in any correspondence. Thank you for shopping with us. 8O

Posted: Thu Feb 23, 2006 10:09 pm
by nickman013
HAHAHAHAHAHAHA :D :mrgreen:

Posted: Sun Feb 26, 2006 12:59 am
by ryos
Thanks for the advice. I'm going with option 1 for now, just because it simplifies the implementation (and they aren't paying me by the line, you know 8)). Editing an entry will be a somewhat rare occurrence (I can't see it happening more than a few times a day, and most days it won't happen at all).

If I'm wrong, option 2 will still be there, waiting for the day it's needed.

Thanks again!