Re: [sqlite] Index performance

On Fri, 15 Nov 2013 12:55:07 + "L. Wood" wrote: > The states are relatively few (50). There are tens of thousands of > companies. > > * If I frequently do queries like this: > "SELECT * FROM Foo WHERE company_stock_symbol='bar' AND state='baz';" > what index should I use?

Re: [sqlite] Index performance

On 15 Nov 2013, at 12:55pm, L. Wood wrote: > The states are relatively few (50). There are tens of thousands of companies. > > * If I frequently do queries like this: > "SELECT * FROM Foo WHERE company_stock_symbol='bar' AND state='baz';" > what index should I use? > Should I

Re: [sqlite] Index performance

On Fri, Nov 15, 2013 at 8:02 AM, Dominique Devienne wrote: > > What I wonder though is how many is "too many distinct values in the > left-most columns", i.e. is 50 too many or not. > > Will there be a pragma for the cut-off number, or there's not really a > cut-off number

Re: [sqlite] Index performance

On Fri, Nov 15, 2013 at 1:55 PM, L. Wood wrote: > Suppose I have a table Foo with two columns: state, company_stock_symbol. > (There are other columns but let's ignore them.) > > The states are relatively few (50). There are tens of thousands of > companies. > I was just

[sqlite] Index performance

Suppose I have a table Foo with two columns: state, company_stock_symbol. (There are other columns but let's ignore them.) The states are relatively few (50). There are tens of thousands of companies. * If I frequently do queries like this: "SELECT * FROM Foo WHERE company_stock_symbol='bar'

Re: [sqlite] Index performance using 2 integers vs. 1 float

Kosenko Max wrote: > > > I think it's better to try go with single integer. It perfectly fits range > 12:00:00 midnight, January 1, 0001 Anno Domini (Common Era) to 11:59:59 > P.M., December 31, A.D. (C.E.) in 100 nanosecond units. And it's good > idea to store all dates in UTC. > >

Re: [sqlite] Index performance using 2 integers vs. 1 float

Simon Slavin-2 wrote: > > > We can't give you much idea because . . . > Another aspect is which fields you need to retrieve when you do your > SELECT. If your select needs to retrieve the time field, and the time > field doesn't appear in the index it's using, it will need to read the

Re: [sqlite] Index performance using 2 integers vs. 1 float

danjenkins wrote: > > Hello, > We use a julian.decimal format to represent date/time (i.e. noon of August > 26, 2009 would be 40049.5000) and we use this julian date for an index > key. Our databases are frequently up to 3GB in size containing 10 million > records with 15 assorted field types

Re: [sqlite] Index performance using 2 integers vs. 1 float

On 27 Aug 2009, at 3:21am, danjenkins wrote: > We use a julian.decimal format to represent date/time (i.e. noon of > August > 26, 2009 would be 40049.5000) and we use this julian date for an > index key. > Our databases are frequently up to 3GB in size containing 10 million > records >

Re: [sqlite] Index performance using 2 integers vs. 1 float

On Wed, Aug 26, 2009 at 9:21 PM, danjenkins wrote: > > Hello, > We use a julian.decimal format to represent date/time (i.e. noon of August > 26, 2009 would be 40049.5000) and we use this julian date for an index key. > Our databases are frequently up to 3GB in size containing 10

[sqlite] Index performance using 2 integers vs. 1 float

Hello, We use a julian.decimal format to represent date/time (i.e. noon of August 26, 2009 would be 40049.5000) and we use this julian date for an index key. Our databases are frequently up to 3GB in size containing 10 million records with 15 assorted field types per record and contain 6 months