On 3/30/06, Dennis Cote <[EMAIL PROTECTED]> wrote: > > > Another approach is to remove your primary key. If you don't need it to > enforce uniqueness constraints on your data then you could eliminate the > primary key, and change the EntryId column into an integer primary key > column. This primary key is not stored as a separate index table, it is > stored in the key fields of the Btree used to hold the data table. > > create table Data ( > EntryId INTEGER PRIMARY KEY, > PropertyId INTEGER, > Value NUMERIC > ); > > Now you truly won't have any indexes, and your inserts and updates will > run as quickly as possible. Also when you search for an EntryId and > PropertyId pair, SQLite will use the index on EntryId to locate the > correct section in the data table quickly, and then it will scan through > the rows with that EntryId sequentially looking for a matching > PropertyId. You didn't say how many properties each entry has, but for > reasonable values this may be faster than fully indexed lookup because > it eliminates half of the disk reads and should reduce the cache > thrashing. This will also reduce the size of your database file to about > half of its current size by eliminating the index and storing the > EntryId in the rowid of the Data table.
Soory about the self reply, but I got to thinking about my suggestion later and realized that this part was complete crap! Integer primary key fields must be unique, so this could only store one property for each entry. Please ignore this blather. Dennis Cote
