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

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
ryos
Forum Newbie
Posts: 16
Joined: Tue Feb 14, 2006 4:55 pm

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

Post 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)
Last edited by ryos on Sun Feb 26, 2006 1:00 am, edited 1 time in total.
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

Yes, option 2 is generally the "good" way.. but honestly, if you're more comfortable with option 1, go for it.
User avatar
Christopher
Site Administrator
Posts: 13596
Joined: Wed Aug 25, 2004 7:54 pm
Location: New York, NY, US

Post 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.
(#10850)
josh
DevNet Master
Posts: 4872
Joined: Wed Feb 11, 2004 3:23 pm
Location: Palm beach, Florida

Post 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
Last edited by josh on Thu Feb 23, 2006 10:07 pm, edited 1 time in total.
User avatar
Christopher
Site Administrator
Posts: 13596
Joined: Wed Aug 25, 2004 7:54 pm
Location: New York, NY, US

Post 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
(#10850)
User avatar
nickman013
Forum Regular
Posts: 764
Joined: Sun Aug 14, 2005 12:02 am
Location: Long Island, New York

Post by nickman013 »

HAHAHAHAHAHAHA :D :mrgreen:
ryos
Forum Newbie
Posts: 16
Joined: Tue Feb 14, 2006 4:55 pm

Post 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!
Post Reply