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

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

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 loo

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

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 time > but actually

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

>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 s

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

annot 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 resending this message below from

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

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 run analyze and then

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

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 don't know how to track

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

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 McM

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

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 any examples where ANA

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

> 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 uploa

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

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 ...> ( y between 100 and 10

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

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 the WHERE clauses? > >

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

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 ...> (