Soft delete could increase the SELECT speed because you have to check always for the "deleted" column. Moreover the DB will grow up without limit if no one physically delete the records: anyway UPDATE of a lot of records could be expensive too. I think the only way is to DELETE more frequently less records. If someone have a smart idea, I'm listening ;)
Il 08/10/2010 22.54, Nicolas Williams ha scritto: > On Fri, Oct 08, 2010 at 05:49:18PM +0100, Simon Slavin wrote: >> On 8 Oct 2010, at 5:48pm, Stephan Wehner wrote: >>> On Fri, Oct 8, 2010 at 7:14 AM, Michele Pradella >>> <michele.prade...@selea.com> wrote: >>>> "science fiction?" was a rhetorically question. I'm only wondering >>>> about what is the best and fastest way to DELETE a lot of records from >>>> huge DB. I know and understand physical limit of data moving: anyway for >>>> now I'm trying to split the BIG DELETE in some smaller DELETE to spread >>>> the time used. It's the only way I can figure out at the moment. >>> Is a soft-delete faster? Then you could add a slow-moving delete >>> (mentioned earlier by Aldes Rossi, for example) >>> for the soft-deleted records. >> Soft-delete ? Is that having another column which is a '1' if the >> record is supposed to exist and a '0' if it's supposed to be deleted ? > Use NULL to indicate deleted-ness. But note that you may still have to > update lots of rows and indexes. Indeed, a true delete might not need > much more I/O (whereas a VACUUM after the DELETE would). > _______________________________________________ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > > -- Selea s.r.l. Michele Pradella R&D SELEA s.r.l. Via Aldo Moro 69 Italy - 46019 Cicognara (MN) Tel +39 0375 889091 Fax +39 0375 889080 *michele.prade...@selea.com* <mailto:michele.prade...@selea.com> *http://www.selea.com* _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
