On Thu, Oct 7, 2010 at 8:05 PM, Michele Pradella <michele.prade...@selea.com > wrote:
> Hi all, I have a question about how to speed up a DELETE statement. > I have a DB of about 3GB: the DB has about 23 millions of records. > The DB is indexed by a DateTime column (is a 64 bit integer), and > suppose you want to delete all records before a date. > Now I'm using a syntax like this (I try all the statement with the > sqlite shell): > suppose to use __int64 DateValue=the date limit you want to delete > > DELETE FROM table_name WHERE DateTime<DateValue > > the statement is trying to delete about 5 millions records and it takes > about 4-5minutes. > Is there a way to try to speed up the DELETE? > I think there is some optimization possible in this case, but I don't know whether it exists in the optimizer currently. It could work like this. If sqlite first deletes entries from the index B-tree while appending rowids to the auto index based on the rowid of the main table (it is a part of the index obviously), the next delete from the main B-tree can use this index to delete faster. I tried to determine existence of such optimization from numbers of reading and writing and it seems that DELETE does separate lookup in the main table for every index entry it found. Does something like this exist in sqlite and if no, worth it or not implementing? Max _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
