Re: [sqlite] Optmize queries on ranges

2018-10-31 Thread Jens Alfke
> On Oct 25, 2018, at 10:45 AM, Keith Medcalf wrote: > > There is an extra column load and compare when using the between version of > the query (this is because although the optimization of the index use is the > same, the use of x BETWEEN y AND z adds both the y <= x and x <= z checks as

Re: [sqlite] Optmize queries on ranges

2018-10-27 Thread E.Pasma
> Keith Medcalf wrote: > .. Am I doing something wrong here .. No! The query with order by + limit 1 is superior, also in my test. Still I am surprised that the rtree extension is available by default (at least in the sqlite version 3.25 command line)

Re: [sqlite] Optmize queries on ranges

2018-10-26 Thread Keith Medcalf
a >Sent: Friday, 26 October, 2018 16:28 >To: SQLite mailing list >Subject: Re: [sqlite] Optmize queries on ranges > >About the rtree extension, which was the first idea. > >The extension appears available without any special installation >option. This is easier than what is

Re: [sqlite] Optmize queries on ranges

2018-10-26 Thread E.Pasma
About the rtree extension, which was the first idea. The extension appears available without any special installation option. This is easier than what is mentioned in https://sqlite.org/rtree.html chapter 2: "Compiling The R*Tree Module". This chapter may as

Re: [sqlite] Optmize queries on ranges

2018-10-26 Thread Keith Medcalf
er, 2018 01:49 >To: sqlite-users@mailinglists.sqlite.org >Subject: Re: [sqlite] Optmize queries on ranges > >Ok, after the message I thought a little bit more. > >And it turns out that in the database the `start`s are not unique how >they >should. >Making them unique, see

Re: [sqlite] Optmize queries on ranges

2018-10-26 Thread Keith Medcalf
e fact that there's a Highway to Hell but only a Stairway to Heaven says a lot about anticipated traffic volume. >-Original Message- >From: sqlite-users [mailto:sqlite-users- >boun...@mailinglists.sqlite.org] On Behalf Of siscia >Sent: Friday, 26 October, 2018 01:27 >To

Re: [sqlite] Optmize queries on ranges

2018-10-26 Thread Olivier Mascia
> Le 26 oct. 2018 à 09:27, siscia a écrit : > > thanks for your suggestions, unfortunately, I already tried all of them, > except for the rtrees. > > Actually, my request for help wasn't complete. > > The ranges I am storing in the table are not overlapping. > > To make an example in SQL. >

Re: [sqlite] Optmize queries on ranges

2018-10-26 Thread siscia
Sorry, I was a little too optimistic. Making the starts unique does help only for some queries, not for all. Why? Cheers, Simone -- Sent from: http://sqlite.1065341.n5.nabble.com/ ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org

Re: [sqlite] Optmize queries on ranges

2018-10-26 Thread siscia
Ok, after the message I thought a little bit more. And it turns out that in the database the `start`s are not unique how they should. Making them unique, seems to solve the performance problem completely. However, still, I am not sure why the `LIMIT 1` does not help at all. Can you guys shed

Re: [sqlite] Optmize queries on ranges

2018-10-26 Thread Dan Kennedy
On 10/26/2018 02:27 PM, siscia wrote: Hi all, thanks for your suggestions, unfortunately, I already tried all of them, except for the rtrees. Actually, my request for help wasn't complete. The ranges I am storing in the table are not overlapping. To make an example in SQL. The following can

Re: [sqlite] Optmize queries on ranges

2018-10-26 Thread siscia
Hi all, thanks for your suggestions, unfortunately, I already tried all of them, except for the rtrees. Actually, my request for help wasn't complete. The ranges I am storing in the table are not overlapping. To make an example in SQL. The following can be in the dataset: INSERT INTO

Re: [sqlite] Optmize queries on ranges

2018-10-25 Thread Keith Medcalf
--- The fact that there's a Highway to Hell but only a Stairway to Heaven says a lot about anticipated traffic volume. On Thursday, 25 October, 2018 10:48, Dan Kennedy wrote: >On 10/25/2018 11:13 PM, siscia wrote: >> >> I am facing an interesting optimization problem. >> >> I have a table

Re: [sqlite] Optmize queries on ranges

2018-10-25 Thread Keith Medcalf
On Thursday, 25 October, 2018 10:48, Dan Kennedy wrote: >On 10/25/2018 11:13 PM, siscia wrote: >> Hi all, >> CREATE TABLE ranges ( >> start int, >> end int, >> value int, >> ); >> The query that I am interested in optimizing is "select value from >> ranges where (? between start

Re: [sqlite] Optmize queries on ranges

2018-10-25 Thread Simon Slavin
On 25 Oct 2018, at 5:13pm, siscia wrote: > CREATE TABLE ranges ( >start int, >end int, >value int, > ); > > The query that I am interested in optimizing is > "select value from ranges > where (? between start and end)" First, "END" is a reserved keyword in SQLite. Your use of

Re: [sqlite] Optmize queries on ranges

2018-10-25 Thread Dan Kennedy
On 10/25/2018 11:13 PM, siscia wrote: Hi all, I am facing an interesting optimization problem. I have a table like this: CREATE TABLE ranges ( start int, end int, value int, ); The query that I am interested in optimizing is "select value from ranges where (? between start and

[sqlite] Optmize queries on ranges

2018-10-25 Thread siscia
Hi all, I am facing an interesting optimization problem. I have a table like this: CREATE TABLE ranges ( start int, end int, value int, ); The query that I am interested in optimizing is "select value from ranges where (? between start and end)" The max performance that I was able