Donald,
> To test this, I think you'd want to create a select for some of the most
> recent data (i.e. data at the tail of the database), perhaps after
> clearing cache. I suspect this will take the full table scan time to
> return any values.
I'd actually just thought of that. All my test SELECTs happened
to be using data close to the "front" of the file. As suspected, if I try
it on "later" data, I have to wait for the table scan, then I get the
data. I'd be erroneously assuming that SQLite was somehow searching for
the first value quickly, then scanning the table.
> Two thoughts:
>
> 1). Easy.
> How bad is the extra 0.8 GByte cost of the index? At today's prices,
> it's only about 20 cents on ordinary 5" drives. (but maybe you're
> programming a cellphone)
No, it's nothing terribly constrained. I'm just trying to
understand the mechanisms and do what I can to keep the size down where
possible. I was somewhat surprised to find that adding an index on a
single INTEGER column nearly doubled the size of the database and wanted
to figure out if there was a way around it, given that the column will
always be sorted. (And given my, perhaps erroneous understanding that
creating an INDEX just makes sure that the column stays sorted so SQLite
can search through it more intelligently)
> 2). Fancy.
> You could create your own sparse index table mapping a ROWID to say,
> every thousandth timestamp. Then you could create upper and lower ROWID
> bounds on any query based on timestamps. Maybe you import the records
> in batches already and can create the new table at the same time.
This is closer to what I'm probably going to do. The data gets
pulled in every 5 minutes, but between runs, very little actually changes.
So the idea is to store only the changes along with a full dump say once
or twice every day. Then I can just query the values from <requested
time> to <last full row store> and compute the state of everything from
that data.
Thanks,
Chris
_______________________________________________
sqlite-users mailing list
[email protected]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
