On 20.08.2010 00:18, Eric Smith wrote:
> 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.
I believe you can do this with an index helper table managed by triggers
on your main table. Use the insert trigger's when clause to control
which records to "index".
The delete statement is obviously a little more complex than an ordinary
delete, but "explain query plan" shows that it uses indexes instead of a
full table scan so it should run quite fast.
Example SQL follows below.
Ralf
----
drop table if exists records;
create table records (id INTEGER PRIMARY KEY, fileid, value);
drop table if exists records_idx;
create table records_idx (id INTEGER PRIMARY KEY, fileid);
create index records_idx_idx on records_idx (fileid);
create trigger records_insert_after after insert on records
when new.fileid = 7
begin
insert into records_idx values (new.rowid, new.fileid);
end;
create trigger records_delete after delete on records
begin
delete from records_idx where id = old.rowid;
end;
insert into records (fileid, value) values (1, 'one');
insert into records (fileid, value) values (7, 'seven 1');
insert into records (fileid, value) values (7, 'seven 2');
insert into records (fileid, value) values (8, 'eight');
select * from records_idx;
delete from records
where rowid in (select rowid from records_idx where fileid = 7);
select * from records;
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
