> Then what is "FROM Pois_bb, Pois WHERE...Pois_bb.Id = Pois.Id"?
> That's joining two tables together.
This is just because of the rtree, which is in Pois_bb
(http://www.sqlite.org/rtree.html), has nothing to do with the second
condition "styleid IN .."
Am 30.05.2017 um 18:29 schrieb David
Thomas Flemming Tue, 30 May 2017 09:43:15 -0700
>> Try putting a "+" symbol before "styleid". Like this:
>>
>> AND +styleid IN (1351,1362,1371,1374,1376,1542,1595,1597,1643,1762)
> THATS IT !! :-)))
>
> 50ms with +, and 15000ms without the +
>
> How is that possible?
Hello, best
If you scroll down in my previous reply I put the explain query plan outputs in
with the queries. Guess I should have mentioned that. (Re-copied them below)
It was using the index on StyleId, thinking that was going to be faster. What
Dr Hipp suggested in adding the unary + operator does is turn
> Try putting a "+" symbol before "styleid". Like this:
>
> AND +styleid IN (1351,1362,1371,1374,1376,1542,1595,1597,1643,1762)
THATS IT !! :-)))
50ms with +, and 15000ms without the +
How is that possible?
Am 30.05.2017 um 17:36 schrieb Richard Hipp:
On 5/27/17, Thomas Flemmin
On 5/27/17, Thomas Flemming wrote:
> Hi,
>
> I have a table Pois with points of interest (geogr. coordinate, label,
> styleid) where I do regional querys using a rtree-index:
>
> SELECT Pois.* FROM Pois_bb, Pois WHERE y0 < -14.8600 AND y1 > -15.12862
>AND x0
[mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im
Auftrag von Thomas Flemming
Gesendet: Dienstag, 30. Mai 2017 18:15
An: sqlite-users@mailinglists.sqlite.org
Betreff: Re: [sqlite] SELECT WHERE with RTREE and second condition slow
force it to go the way you want by using "cross joi
:15
An: sqlite-users@mailinglists.sqlite.org
Betreff: Re: [sqlite] SELECT WHERE with RTREE and second condition slow
> force it to go the way you want by using "cross join" to force the
> ordering of
How would such "cross join" statemant look like?
Am 30.05.2017 um 1
:sqlite-users-boun...@mailinglists.sqlite.org] On
Behalf Of Thomas Flemming
Sent: Monday, May 29, 2017 9:28 AM
To: sqlite-users@mailinglists.sqlite.org
Subject: Re: [sqlite] SELECT WHERE with RTREE and second condition slow
Ok, here is a sample to try these queries:
http://files.qvgps.com/0-tom-
s join" to force the ordering of the join.
-Original Message-
From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On
Behalf Of Thomas Flemming
Sent: Monday, May 29, 2017 9:28 AM
To: sqlite-users@mailinglists.sqlite.org
Subject: Re: [sqlite] SELECT WHERE with RTREE
On May 30, 2017 10:07:45 AM EDT, Thomas Flemming wrote:
>Style.Id doesn't need to be LONG, you're right. I changed it but it
>doesn't
>make a difference.
>Pois.Id need to be LONG because the source for this column is really
>containing 64-bit values
Integers in SQLite are of variable size; if
-Original Message-
From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On
Behalf Of Thomas Flemming
Sent: Tuesday, May 30, 2017 8:08 AM
To: sqlite-users@mailinglists.sqlite.org
Subject: Re: [sqlite] SELECT WHERE with RTREE and second condition slow
> > Do yo
to:sqlite-users-boun...@mailinglists.sqlite.org] Im
Auftrag von Thomas Flemming
Gesendet: Dienstag, 30. Mai 2017 16:08
An: sqlite-users@mailinglists.sqlite.org
Betreff: Re: [sqlite] SELECT WHERE with RTREE and second condition slow
> Do you know which SQLite version is being used by SQLite Expert
> Do you know which SQLite version is being used by SQLite Expert
> Professional 3.5?
sqlite 3.10.0
I tried SQLite Expert Professional 4, using sqlite 3.18.0, but its the same
slow.
Style.Id doesn't need to be LONG, you're right. I changed it but it doesn't
make a difference.
Pois.Id need to b
Am Mon, 29 May 2017 14:27:56 +0100 schrieb Thomas Flemming:
> Ok, here is a sample to try these queries:
>
> http://files.qvgps.com/0-tom-public/Geonames_World_2017.zip
> (825mb, 12 mio records)
Just a few quick observations ...
First, I would replace all column declarations like
LONG PRIMARY
Ok, here is a sample to try these queries:
http://files.qvgps.com/0-tom-public/Geonames_World_2017.zip
(825mb, 12 mio records)
Before I change my app-logic to do the styleid-query on the app-side, I would
like to know, if there might be a chance to get this fast on the sqlite-side.
very fast
Morning,
> Does ANALYZE gather statistical data about rtree virtual tables? I seem to
ANALYZE doesn't help.
I'm busy preparing and uploading a sample-db, then it might be easier to
figure that out.
Tom
Am 28.05.2017 um 11:01 schrieb Wolfgang Enzinger:
Am Sat, 27 May 2017 19:20:00 -0400 schrie
Am Sat, 27 May 2017 19:20:00 -0400 schrieb Richard Hipp:
> On 5/27/17, Thomas Flemming wrote:
>> Hi,
>>
>> I have a table Pois with points of interest (geogr. coordinate, label,
>> styleid) where I do regional querys using a rtree-index:
>>
>> SELECT Pois.* FROM Pois_bb, Pois WHERE y0 < -14.8600
On 5/27/17, Thomas Flemming wrote:
> Hi,
>
> I have a table Pois with points of interest (geogr. coordinate, label,
> styleid) where I do regional querys using a rtree-index:
>
> SELECT Pois.* FROM Pois_bb, Pois WHERE y0 < -14.8600 AND y1 > -15.12862
>AND x0
Please post the output of the following command from the command-line shell:
.fullschema --indent
To capture the output of the above command, you can preceed it by
".once out.txt":
.once out.txt
.fullschema --indent
To emphasize, use the command-line shell "sqlite3.exe" which you ca
Hi,
I have a table Pois with points of interest (geogr. coordinate, label,
styleid) where I do regional querys using a rtree-index:
SELECT Pois.* FROM Pois_bb, Pois WHERE y0 < -14.8600 AND y1 > -15.12862
AND x0 < 30.46203 AND x1 > 30.00074766
20 matches
Mail list logo
