Thread: DELETE and efficiency
I am working on an application with very dynamic data: it gets added and deleted often. How effecient are the searches in this situation? In other words: most DB situations I've seen don't use DELETE, and so the underlying algorithms are probably optimized for this. Is using DELETE going to kill my performance? Thanks, John Gateley, gateley@jriver.com
On Tue, Mar 06, 2001 at 03:21:19PM -0600, gateley@jriver.com wrote: > I am working on an application with > very dynamic data: it gets added and > deleted often. How effecient are the > searches in this situation? [...] > Is using DELETE going to kill my performance? In short, no. I don't think so, at any rate. A DELETE is just like any query, so you'll need to fine tune the queries, make indexes, look at the output of EXPLAIN, etc. But I can't see why a DELETE would be any slower than most other queries. But as I understand it, a DELETE just marks the row as deleted -- the data is not actually removed from disk. This means they should be fast, but it also means that doing a VACUUM [ANALYZE] fairly regularly is probably a good idea. HTH, Neil -- Neil Conway <neilconway@home.com> Get my GnuPG key from: http://klamath.dyndns.org/mykey.asc Encrypted mail welcomed You can use facts to prove anything that's even remotely true. -- Homer J. Simpson
On Tuesday 06 March 2001 05:05 pm, Neil Conway wrote: > > But as I understand it, a DELETE just marks the row as deleted -- the > data is not actually removed from disk. This means they should be > fast, but it also means that doing a VACUUM [ANALYZE] fairly regularly > is probably a good idea. Is that so? If so, that means there might be a way to do an undelete? What is it? Michelle -- ------------ Michelle Murrain, Ph.D. President Norwottuck Technology Resources mpm@norwottuck.com http://www.norwottuck.com
Michelle Murrain writes: > Is that so? If so, that means there might be a way to do an > undelete? What is it? As I understand it there's no way to do an undelete because of the way that transactions get handled, but it does suggest that there are places where a "deleted char(1)" (or however you want to implement it) field can be quite handy, especially given the ridiculously cheap price of disk space (modulo backup...) nowadays. In applications, confirmation is a cop-out for those too lazy to implement undo (okay, that's overstating the case a bit), but structuring your data to acknowledge this at the beginning can be a worthwhile exercise. Dan