Hello,
I'm a relative newcommer to SQL and SQLite and have was is likely
a silly question. However, I've not found an answer to it or even a
reference to anything similar in searching the list archives or the web.
If I have a table laid out thusly:
CREATE TABLE bridge_table (
timestamp INTEGER NOT NULL,
hID INTEGER NOT NULL,
sID INTEGER NOT NULL,
pID INTEGER NOT NULL
);
where timestamp values (unix 32-bit integer timestamps) would always be
inserted into the database in ascending order, and where there may be as
many as 500 hID entries for each timestamp. After a while, this table
will grow to contain between 30 and 50 million records.
In doing some tests, I find that with the fully populated table, I
can run:
SELECT * from bridge_table WHERE timestamp = <someval>;
and almost immediately get back all the results I want, since they're
right next to each other in the db. However, the actual SELECT doesn't
return for almost 13 additional seconds, as the entire table is scanned
for other rows where timestamp might be <someval>.
Of course, the simple answer is to add an index on timestamp,
which does what I want. However, doing so increases the size of this
50 million row table from 1GB to 1.8GB. It seems to me that if I could
convince SQLite that the rows would always be inserted in ascending order
by timestamp, that I could have a sort of implicit index without consuming
the additional 0.8GB.
Is this even theoretically possible or am I missing something
obvious that would prevent an "implicit index" from working like I'm
suggesting?
Thanks,
Chris
_______________________________________________
sqlite-users mailing list
[email protected]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
