On 4/14/08, Chris Tracy <[EMAIL PROTECTED]> wrote: > 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?
Have you considered making timestamp a PRIMARY KEY? So, declare it as INTEGER PRIMARY KEY NOT NULL (the NOT NULL may be unnecessary at that point). That should help. > > Thanks, > > Chris > _______________________________________________ > sqlite-users mailing list > [email protected] > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > -- Puneet Kishor http://punkish.eidesis.org/ Nelson Institute for Environmental Studies http://www.nelson.wisc.edu/ Open Source Geospatial Foundation (OSGeo) http://www.osgeo.org/ _______________________________________________ sqlite-users mailing list [email protected] http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
