Simon Slavin wrote:
> http://www.sqlite.org/lang_createview.html
>
> This is the SQL standard way to reduce your view of a table to just
> certain rows. If I understand your request, this feature should provide
> exactly what you want. Appropriate indexes will be used when consulting
> any VIEW you've defined.
I don't think that helps either. A view in sqlite is just syntactic
sugar for a select statement. I don't want to define any real indices
-- they are a performance burden.
> > Something like DELETE FROM records WHERE __recno IN (SELECT __recno
> > FROM idxTable), where __recno is the INTEGER PRIMARY KEY on records.
>
> I don't understand what you're looking up here. If you have some
> method of recognising which rows of a table should be deleted just use the
> appropriate
>
> DELETE FROM ... WHERE ...
>
> command. No need for any sub-SELECT clause.
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.
So the next cut is to say "create index recordsIdxFileId on
records(fileId)".
But that is bad because it is a huge burden during INSERT and is not
used often enough (or with enough values) to justify its existence.
What I really want is to be able to say "create index
recordsIdxFileIdOnFile3 on records(fileId) where fileId=7".
But sqlite doesn't do that.
So let's assume SQLite is much faster at deleting rows by the INTEGER
PRIMARY KEY than it is by deleting rows by some other value. Then we
can optimize by keeping track of which __recnos we will want to delete.
Hence my idea for having a separate table that maps fileId --> __recno,
but only for the fileId we care about.
Eric
--
Eric A. Smith
Keeping Young #6:
Don't look back. Something might be gaining on you.
-- Satchel Paige
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
