Sorry - thats what I mean - the table (as redefined by simon)
create table lookup (rowindex int, rangestart int, rangeend int)
values in rowindex are not unique so I cannot make this column a primary
key so i am not sure whether RTrees would be any help in this scenario
Hi Paul,
Firstly, the RTRee will work spectacularly well for your case, but you are in no way bound by the rowid or primary keys to conform
to your row id, but if your row ids are not unique, then you need to simply use a different key for that function.
For instance, 2 tables, one RTree table witht he standard Primary key + start +
end range columns,
Second your data table with Key, rowindex, field3, field4, etc. starting with a Key which should be a primary key or (best be
INTEGER PRIMARY KEY) or a foreign key on the Primary Key of the RTree table (I wont go into foreign key relationships here, you can
read it on the SQLite web pages, it's only an option, you can very well simply refer the id's in Select queries).
The second column being your rowindex which may very well be non-unique and void of all other restrictions even, followed by the
rest of however many data columns are needed.
Think of the RTree table as a sort of ranged-Index to your data table. So you
can do queries like:
SELECT * from myData AS D JOIN rtreeTable AS R ON (R.PrimKey=D.Key) WHERE
(Range-select-values);
This will return all the data in your data table (which may very well again re-include the actual ranged values if space is not a
concern) according to the lookup index IDs supplied from the RTree efficient search algorithm.
This select above can take many other forms but as long as you are looking for a range specifier, the Rtree table will be much
faster on the lookup, and it returns Key alues from an Index, which is no effort to simply look up for the data table. Also, never
be afraid of using an additional index, the more ways SQLite can find your data, the better it should be at picking the fastest.
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
