Re: [sqlite] table format for most efficient query

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

Re: [sqlite] table format for most efficient query

On 7/5/2013 10:59 AM, Paul Sanderson wrote: 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

Re: [sqlite] table format for most efficient query

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 On 5 July 2013 14:39,

Re: [sqlite] table format for most efficient query

On 7/5/2013 9:13 AM, Paul Sanderson wrote: my primary key would not be unique :( That's an oxymoron - primary key is unique, by definition. -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org

Re: [sqlite] table format for most efficient query

Having read up on RTrees I may have a problem - my primary key would not be unique :( On 5 July 2013 12:05, Paul Sanderson wrote: > Thanks for that Igor - I had RTree in the back of my mine but couldn't > remember what or where I had read about it. > > before I

Re: [sqlite] table format for most efficient query

Thanks for that Igor - I had RTree in the back of my mine but couldn't remember what or where I had read about it. before I compile a dll (or someone provides a link to a 32 bit dl with RTree enabled) is RTree likely to be useful in the following scenario? Each look up will usually, but not

Re: [sqlite] table format for most efficient query

On 7/4/2013 5:29 PM, Paul Sanderson wrote: select * from master as m, lookup as l where x >= start and and x < end and m.index = l.index You might want to look at the RTree module: http://www.sqlite.org/rtree.html It's specifically designed to implement such range queries efficiently. --

Re: [sqlite] table format for most efficient query

On Thu, 4 Jul 2013 22:52:26 +0100, Simon Slavin wrote: > > I assume you missed a comma: > > create table lookup (index int, start int, end int) indeed > But actually it’s a bad idea to use the words > 'index' and 'end’ for columns because they're > used as reserved words

Re: [sqlite] table format for most efficient query

Thanks Simon - i'll have a play. tomorrow On 4 July 2013 22:52, Simon Slavin wrote: > > On 4 Jul 2013, at 10:29pm, Paul Sanderson > wrote: > > > create table lookup (index int, start int end int) > > I assume you missed a comma: > > create

Re: [sqlite] table format for most efficient query

On 4 Jul 2013, at 10:29pm, Paul Sanderson wrote: > create table lookup (index int, start int end int) I assume you missed a comma: create table lookup (index int, start int, end int) But actually it’s a bad idea to use the words 'index' and 'end’ for columns