Mysql - Deleting items omitted from an insert|update

July 7, 2006 2:01 PM

MysqlFilter: I need someone with mighty mysql-Fu to help me out here. I have a list of items stored in my database (60 or so per user), they get sent to the flash application that I have, then once the user is done with everything, they press "save" (20 to 60 times a day), and it saves the list of items back to the database. I was doing an insert on duplicate update, but this doesnt get rid of the items that may have been deleted.

So my question is this, should I:

A) Delete all items first, then insert all new records.

B) Keep a list of things that have been deleted in the program, and send that list back with all of the other data.

C) Add a last modified timestamp and delete anything that wasn`t updated in the last minute


This isn`t a huge application, and definitely won`t be public... I can`t imagine more than two people using this concurrently, but beings that the mysql database is on my desktop, its in my best interest if I keep things quick, and in everyone`s best interest if I keep things responsive.

Will I notice if I just delete&insert? What would the difference in load be? Would it be worth my time to program in B or C?

Thanks!

posted by hatsix to Technology (10 answers total)

It sounds like the obstacle here is your interface and the underlying code behind it, not a very difficult mysql problem.

If you can detect in your interface that an item has been changed or marked for deletion, delete it or updated it in the database accordingly, otherwise leave it alone. Basically your b and c combined above.


However, if you don't mind a tremendously kludgy solution, a will work - but even with just 2 people using it concurrently, that's a dangerous one.


Is it very important to be able to edit multiple items really quickly? Could you just list each of the items and have:

- at the top of the list, an add button to add a new item
- next to items, a "delete" button and an "edit" button allowing to delete/update those items...

Or would that type of interface slow down your process too much?
posted by twiggy at 2:28 PM on July 7, 2006

Oh, as far as load: deleting and reinserting 60 items isn't all that much server load.. it's just kludgy and potentially dangerous.
posted by twiggy at 2:29 PM on July 7, 2006

If I understand your application, the flash app has a local copy of the user's items. They can muck around with these, including adding & deleting. When they hit save you want the database to get updated with whatever their data looked like.

I would say B sounds like the best bet and the least error prone.

If you do go this route, don't iterate over the deletes in the application, or you will notice a performance hit. Instead, make your query like "DELETE FROM `items` WHERE Item_ID IN ('deleted1','deleted2','deleted3'...'lastdeleted')".

If you decide to go A, don't delete all items, just delete the ones that belong to that particular user.
posted by justkevin at 2:40 PM on July 7, 2006

Response by poster: The program itself allows the user to put objects on the screen and draw relationships between them... It's meant to work online or offline, but edits only happen online. I like the idea of having all of the updates sent at one time, rather than the ajaxy approach of editing things one at a time. (for instance, they can drag and drop things around on the screen, and this would produce way too many updates.

I'm using an XML document to go back and forth with everything, which allows me to take the program offline for presentation (i.e. no editing) purposes.


I should say that while people will be using the database concurrently, they will never be working on the same set of data concurrently. I know A is kludgy, but since two users can't work on the same data at the same time, it should be just as safe as any other way. Plus it's by far the easiest... I guess I was looking for someone who'd talk me into doing it the 'right' way with a really show-stopper reason.
posted by hatsix at 2:47 PM on July 7, 2006

If you've only got two users max using at once, why not just lock the DB while it's being written to -- any other user trying to save will get a "Saving" bar. Then you solve the problem of updating records however you wish.

There's information about table locking in the online MySQL documentation.
posted by fishfucker at 2:50 PM on July 7, 2006

If you go with A, just pray that your server never crashes between the DELETE and INSERT statements.
posted by turaho at 3:03 PM on July 7, 2006

If you delete everything first and then rebuild it, you have a potential crash problem where the user could lose all his/her data. This makes users unhappy.
posted by Malor at 3:54 PM on July 7, 2006

I'd make "delete me" an item value, and have the frontend handle it just like any other item value.
posted by flabdablet at 6:37 PM on July 7, 2006

If you go with A, just pray that your server never crashes between the DELETE and INSERT statements.

This isn't a problem with transactions. (A) with transactions is probably the way I'd go.
posted by sbutler at 7:23 PM on July 7, 2006

As you're dealing with seemingly small amounts of data, you could tag data saved with each update with a unique version ID and leave old data in the table. The data would be safer, and you might want to allow users to undo changes in the future. In a few months' time you could write a simple script to periodically remove any ancient data clogging up the database.

If the data's important you need to be careful and either steer clear of immediate deletion or at least use transactions.
posted by malevolent at 3:28 AM on July 8, 2006