Re: [sqlite] Discrepancy with indexing and WHERE clause with AND/OR

2014-11-18 Thread RSmith
On 2014/11/17 14:48, RP McMurphy wrote: Upon further analysis it appears that the data "shape" is different in different periods within the table. That is, some sections have the inverse shape to other sections. So it looked like query times would change over time but actually they are changing

Re: [sqlite] Discrepancy with indexing and WHERE clause with AND/OR

2014-11-18 Thread RSmith
On 2014/11/17 14:48, RP McMurphy wrote: PS: Some administravia; Does anyone know of a way to reduce the posting delay for this list? Is it always like this? Or is it some problem with the gmane site in general? Join the mailing list directly (you can unsubscribe once you got what you were

Re: [sqlite] Discrepancy with indexing and WHERE clause with AND/OR

2014-11-17 Thread Simon Slavin
On 17 Nov 2014, at 12:48pm, RP McMurphy wrote: > Upon further analysis it appears that the data "shape" is different in > different periods within the table. That is, some sections have the inverse > shape to other sections. So it looked like query times would change over

Re: [sqlite] Discrepancy with indexing and WHERE clause with AND/OR

2014-11-17 Thread RP McMurphy
>When you say the data changes, does the shape actually change? Because all that >matters to analyze is the shape of the data, not the data itself. Upon further analysis it appears that the data "shape" is different in different periods within the table. That is, some sections have the inverse

Re: [sqlite] Discrepancy with indexing and WHERE clause with AND/OR

2014-11-16 Thread Hick Gunter
that xBestIndex return values cannot coax into performing well. -Urspr√ľngliche Nachricht- Von: RP McMurphy [mailto:rpm0...@yahoo.com] Gesendet: Freitag, 14. November 2014 15:32 An: sqlite-users@sqlite.org Betreff: Re: [sqlite] Discrepancy with indexing and WHERE clause with AND/OR I am resend

Re: [sqlite] Discrepancy with indexing and WHERE clause with AND/OR

2014-11-15 Thread RSmith
On 2014/11/14 16:32, RP McMurphy wrote: On Tue, 11/11/14, RP McMurphy wrote: > If you can provide any examples where ANALYZE makes a query slower, I suspect the developer team would like > to see them. > After we

Re: [sqlite] Discrepancy with indexing and WHERE clause with AND/OR

2014-11-15 Thread Simon Slavin
On 14 Nov 2014, at 2:32pm, RP McMurphy wrote: > After we run analyze and then > let the process run for a while the DB > contents change - and it can change quite > considerably depending > upon what is > happening > > I suspect that the analyze data gets stale, but > I

Re: [sqlite] Discrepancy with indexing and WHERE clause with AND/OR

2014-11-15 Thread RP McMurphy
I am resending this message below from 3 days ago because it never made it to the list. RP PS: Messages seem to take a long time to go through the gmane system, at least half a day and sometimes more in my experience so far. On Tue, 11/11/14, RP

Re: [sqlite] Discrepancy with indexing and WHERE clause with AND/OR

2014-11-11 Thread Simon Slavin
On 10 Nov 2014, at 10:55pm, RP McMurphy wrote: >> > > Okay, for my simplified example analyze does improve the times. But for our > application this does not help, and it also harmed a couple of other queries. If you can provide

Re: [sqlite] Discrepancy with indexing and WHERE clause with AND/OR

2014-11-11 Thread RP McMurphy
> Okay, for my simplified example analyze does improve the times. But for our application this does not help, and it also harmed a couple of other queries. I'll have to see if I can make a better example schema showing the problem because I can't

Re: [sqlite] Discrepancy with indexing and WHERE clause with AND/OR

2014-11-10 Thread Simon Davies
On 10 November 2014 16:03, Don V Nielsen wrote: > Isn't this the result of the results cache? The two queries are identical. > The query plan changes... . . . sqlite> explain query plan select count(*) from v wherez = 0 and ...> (

Re: [sqlite] Discrepancy with indexing and WHERE clause with AND/OR

2014-11-10 Thread Don V Nielsen
Isn't this the result of the results cache? The two queries are identical. On Mon, Nov 10, 2014 at 9:26 AM, Clemens Ladisch wrote: > RP McMurphy wrote: > > Is there a way we can make the w index work with both queries and not > > have to run external loops to flatten all

Re: [sqlite] Discrepancy with indexing and WHERE clause with AND/OR

2014-11-10 Thread Clemens Ladisch
RP McMurphy wrote: > Is there a way we can make the w index work with both queries and not > have to run external loops to flatten all the WHERE clauses? sqlite> .timer on sqlite> select count(*) from v wherez = 0 and ...>

[sqlite] Discrepancy with indexing and WHERE clause with AND/OR

2014-11-10 Thread RP McMurphy
If there is a large table and we need to select a subset of values using a WHERE clause with an AND/OR construct sqlite has trouble finding the answer in a reasonable time. Breaking the queries down into separate SELECT statements speeds up the process exponentially. For example the following