On 4/14/08, Dennis Cote <[EMAIL PROTECTED]> wrote: > Jeffrey Rennie wrote: > > On Mon, Apr 14, 2008 at 9:48 AM, Chris Tracy <[EMAIL PROTECTED]> wrote: > > > >>>> 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 > >>> Have you considered making timestamp a PRIMARY KEY? > >>> > >>> So, declare it as INTEGER PRIMARY KEY NOT NULL > >> Unfortunately, the timestamps aren't unique, so I can't use > >> PRIMARY KEY to solve the problem. (Each run generates as many as 500 > >> entries, all with the same timestamp) > >> > > > > Are there ever identical rows? If not, just make the whole row a primary > > key: > > > > CREATE TABLE bridge_table ( > > timestamp INTEGER NOT NULL, > > hID INTEGER NOT NULL, > > sID INTEGER NOT NULL, > > pID INTEGER NOT NULL, > > * PRIMARY KEY (timestamp, hID, sID, pID) > > *); > > > This is a good suggestion.
ummm no. The OP very clearly states that an INDEX is out of question because of space constraints. > Even if there are identical rows, you can > still use and index on all columns, without making it an primary key > (i.e. unique index). > > > CREATE TABLE bridge_table ( > timestamp INTEGER NOT NULL, > hID INTEGER NOT NULL, > sID INTEGER NOT NULL, > pID INTEGER NOT NULL > > ); > CREATE INDEX bt_all ON bridge_table(timestamp, hID, sID, pID); > > This will double the size of the database file and double the row insert > time, but SQLite has an optimization that allows it to use the columns > stored in the index to supply the results of a query without looking at > the main table. In effect this index becomes your main table because it > stores all the data. > > HTH > > Dennis Cote > > > > > _______________________________________________ > 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
