Re: [sqlite] The problem with index

2007-06-20 Thread Dan Kennedy
> Another question: Is it correct that virtual tables can be created using > Perl but not Tcl? I don't have a current need (with the possible > exception of FTS1/2, which are already accessible from Tcl), but the > situation seemed curious. Wondering whether there was an undocumented >

Re: [sqlite] The problem with index

2007-06-20 Thread Gerry Snyder
[EMAIL PROTECTED] wrote: This gives a different answer because the EXCEPT operator makes the rows of the result set unique. So the result set will be: 1 2 111 where as formerly it was 1 1 2 2 111 111 Thank you for the clarification. That is not the behavior I would have expected. In my

Re: Re[2]: [sqlite] The problem with index

2007-06-20 Thread Ken
Joe Wilson <[EMAIL PROTECTED]> wrote: --- "Sergey M. Brytsko" wrote: > But what about the following values: > > 1 1 2 2 100 100 100 100 100 100 100 100 100 100 100 111 111 I guess SQLite's query optimizer could take the cardinality of the column into account via its ANALYZE statistics for

Re: [sqlite] The problem with index

2007-06-20 Thread drh
Gerry Snyder <[EMAIL PROTECTED]> wrote: > Igor Tandetnik wrote: > > Sergey M. Brytsko <[EMAIL PROTECTED]> > > wrote: > >> But what about the following values: > >> > >> 1 1 2 2 100 100 100 100 100 100 100 100 100 100 100 111 111 > > > > If you susptect your data is likely to look like this, you

Re: Re[2]: [sqlite] The problem with index

2007-06-20 Thread Joe Wilson
--- "Sergey M. Brytsko" wrote: > But what about the following values: > > 1 1 2 2 100 100 100 100 100 100 100 100 100 100 100 111 111 I guess SQLite's query optimizer could take the cardinality of the column into account via its ANALYZE statistics for inequality comparisons. It's just the small

Re[2]: [sqlite] The problem with index

2007-06-20 Thread Sergey M. Brytsko
But what about the following values: 1 1 2 2 100 100 100 100 100 100 100 100 100 100 100 111 111 Thanks. -- Sergey Wednesday, June 20, 2007, 3:21:25 PM, wrote: JW> Say you have the following values for BBB: JW> 1 2 3 10 20 30 50 70 80 80 90 100 101 110 110 120 120 150 190 200 JW> How is

Re: [sqlite] The problem with index

2007-06-20 Thread Joe Wilson
--- "Sergey M. Brytsko" wrote: > The problem is the index is NOT used for query: > SELECT BBB FROM XXX WHERE BBB <> 100; > > but in case of query > SELECT BBB FROM XXX WHERE BBB > 100; > all is ok ... > The indices are very important for me, how should I build these queries? Say you have the