> Maybe it'll be clearer if I describe my (quite simple) use case. Our > app is caching what are basically csv files. Hundreds of files, about 2m > records per file. Sometimes we want to delete all the cache rows for one > of the files. We know ahead of time which file it will be -- let's say > it's file 7. > > The schema is roughly > > create table records(__recno INTEGER PRIMARY KEY, fileId, data); > > So sometimes we would *like* to say "delete from records where > fileId=7". > > But that is bad because does a full table scan. > >
If your csv inserts are made as a whole, what about keeping track of rowid ranges (rowidfrom..rowitto) for every csv insert in a separate table? You can be sure it will be a new range not intersecting with any previous if rowid is AUTOINCREMENT (or you can provide your own id starting current max(id) to be absolutely sure). In this case you will get fast deletes and you no longer need an extra indexed field. Max _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
