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)
[SOLVED] Advantage of UPDATE vs. DELETE/INSERT?
Moderator: General Moderators
[SOLVED] Advantage of UPDATE vs. DELETE/INSERT?
Last edited by ryos on Sun Feb 26, 2006 1:00 am, edited 1 time in total.
- Christopher
- Site Administrator
- Posts: 13596
- Joined: Wed Aug 25, 2004 7:54 pm
- Location: New York, NY, US
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
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.
- Christopher
- Site Administrator
- Posts: 13596
- Joined: Wed Aug 25, 2004 7:54 pm
- Location: New York, NY, US
- nickman013
- Forum Regular
- Posts: 764
- Joined: Sun Aug 14, 2005 12:02 am
- Location: Long Island, New York
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
). 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!
If I'm wrong, option 2 will still be there, waiting for the day it's needed.
Thanks again!