> But the rows he wants to delete are those with DateTime<cutoffdate. So > without an index on that column SQL can't find which rows to delete quickly !
"Quickly" is appropriate for one row. For several rows SQLite will sequentially scan the index and for each rowid found there it will traverse the table's b-tree structure from top to bottom searching for the rowid and then delete that row. For 3 million rows it's well likely that this process is slower than sequentially scanning the table and marking as deleted all rows satisfying the condition. Pavel On Thu, Oct 7, 2010 at 12:52 PM, Simon Slavin <slav...@bigfraud.org> wrote: > > On 7 Oct 2010, at 5:36pm, Jay A. Kreibich wrote: > >> On Thu, Oct 07, 2010 at 05:22:19PM +0100, Simon Slavin scratched on the wall: >>> On 7 Oct 2010, at 5:05pm, Michele Pradella wrote: >>> >>>> The DB is indexed by a DateTime column (is a 64 bit integer) >>> >>> Do make sure that that column is declared as INTEGER and that there >>> is an index on it. >> >> When deleting 20 to 25% of the rows, an index is likely to slow >> things down. > > But the rows he wants to delete are those with DateTime<cutoffdate. So > without an index on that column SQL can't find which rows to delete quickly ! > > Hmm. If all rows are entered in DateTime order, and the table has an > AUTOINCREMENT primary key, then the values in that column will be in the same > order as the values in the primary key. So perhaps he could do one SELECT to > find the appropriate primary key value, then use the primary key in his > DELETE command instead of the DateTime column. > > Simon. > _______________________________________________ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
