Thread

1. DELETE and efficiency

   John Gateley <gateley@jriver.com> — 2001-03-06T21:21:19Z

   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
   
   

2. Re: DELETE and efficiency

   Neil Conway <nconway@klamath.dyndns.org> — 2001-03-06T22:05:26Z

   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
   
   

3. Re: DELETE and efficiency

   Michelle Murrain <mpm@norwottuck.com> — 2001-03-06T22:08:41Z

   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
   
   

4. Re: DELETE and efficiency

   Dan Lyke <danlyke@flutterby.com> — 2001-03-08T20:38:44Z

   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