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. 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
