SELECT WHERE with RTREE and second condition slow

classic Classic list List threaded Threaded
20 messages Options
Reply | Threaded
Open this post in threaded view
|

SELECT WHERE with RTREE and second condition slow

tf@qvgps.com
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
                                   AND   18 BETWEEN z0 AND z1
                                   AND   Pois_bb.Id = Pois.Id
Thats very fast, 50ms.

The problem is, when I add a second condition to get certain poi-types only
in the area:

AND styleid IN 1351,1362,1371,1374,1376,1542,1595,1597,1643,1762

The query becomes really slow, 800ms.
There is of course also an index on styleid.

I also realized, just this query:

SELECT * FROM Pois WHERE styleid IN
1351,1362,1371,1374,1376,1542,1595,1597,1643,1762

is also slow for the first call. The second call is fast.

(Using SQLite Expert Professional 3.5 for testing).

Any ideas, how to speed this up?

Thanks
Tom


--
/****************************************
**   Flemming Software Development CC
**   Thomas Flemming
**   PO Box 81244
**   Windhoek, Namibia
**   http://www.quovadis-gps.com
**   mail  [hidden email]
**   +264 (0)81 3329923
**   +49  (0)6182 8492599
***************************************/
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: SELECT WHERE with RTREE and second condition slow

Richard Hipp-3
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 can
obtain from the https://sqlite.org/download.html page.  This is not a
3rd-party tool like SQLite Expert Professional.

On 5/27/17, Thomas Flemming <[hidden email]> 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 < 30.46203 AND  x1 > 30.00074766
>                                    AND   18 BETWEEN z0 AND z1
>                                    AND   Pois_bb.Id = Pois.Id
> Thats very fast, 50ms.
>
> The problem is, when I add a second condition to get certain poi-types only
> in the area:
>
> AND styleid IN 1351,1362,1371,1374,1376,1542,1595,1597,1643,1762
>
> The query becomes really slow, 800ms.
> There is of course also an index on styleid.
>
> I also realized, just this query:
>
> SELECT * FROM Pois WHERE styleid IN
> 1351,1362,1371,1374,1376,1542,1595,1597,1643,1762
>
> is also slow for the first call. The second call is fast.
>
> (Using SQLite Expert Professional 3.5 for testing).
>
> Any ideas, how to speed this up?
>
> Thanks
> Tom
>
>
> --
> /****************************************
> **   Flemming Software Development CC
> **   Thomas Flemming
> **   PO Box 81244
> **   Windhoek, Namibia
> **   http://www.quovadis-gps.com
> **   mail  [hidden email]
> **   +264 (0)81 3329923
> **   +49  (0)6182 8492599
> ***************************************/
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>


--
D. Richard Hipp
[hidden email]
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: SELECT WHERE with RTREE and second condition slow

Richard Hipp-3
In reply to this post by tf@qvgps.com
On 5/27/17, Thomas Flemming <[hidden email]> 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 < 30.46203 AND  x1 > 30.00074766
>                                    AND   18 BETWEEN z0 AND z1
>                                    AND   Pois_bb.Id = Pois.Id
> Thats very fast, 50ms.
>
> The problem is, when I add a second condition to get certain poi-types only
> in the area:
>
> AND styleid IN 1351,1362,1371,1374,1376,1542,1595,1597,1643,1762
>
> The query becomes really slow, 800ms.
> There is of course also an index on styleid.
>
> I also realized, just this query:
>
> SELECT * FROM Pois WHERE styleid IN
> 1351,1362,1371,1374,1376,1542,1595,1597,1643,1762
>
> is also slow for the first call. The second call is fast.
>
> (Using SQLite Expert Professional 3.5 for testing).
>
> Any ideas, how to speed this up?

Have you tried running ANALYZE on your database?
--
D. Richard Hipp
[hidden email]
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: SELECT WHERE with RTREE and second condition slow

Wolfgang Enzinger
Am Sat, 27 May 2017 19:20:00 -0400 schrieb Richard Hipp:

> On 5/27/17, Thomas Flemming <[hidden email]> 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 < 30.46203 AND  x1 > 30.00074766
>>                                    AND   18 BETWEEN z0 AND z1
>>                                    AND   Pois_bb.Id = Pois.Id
>> Thats very fast, 50ms.
>>
>> The problem is, when I add a second condition to get certain poi-types only
>> in the area:
>>
>> AND styleid IN 1351,1362,1371,1374,1376,1542,1595,1597,1643,1762
>>
>> The query becomes really slow, 800ms.
>> There is of course also an index on styleid.
>>
>> I also realized, just this query:
>>
>> SELECT * FROM Pois WHERE styleid IN
>> 1351,1362,1371,1374,1376,1542,1595,1597,1643,1762
>>
>> is also slow for the first call. The second call is fast.
>>
>> (Using SQLite Expert Professional 3.5 for testing).
>>
>> Any ideas, how to speed this up?
>
> Have you tried running ANALYZE on your database?

Does ANALYZE gather statistical data about rtree virtual tables? I seem to
remember that this is not the case.

I don't have an appropriate database at hand right now since I'm busy
currently with a different project, but I seem to remember that I observed
something similar: queries with an rtree table involved seem to *always*
prefer the spatial index over any other index, even if the clipping
embraces the complete extent of graphical data. This is unefficient
especially when the "related table" (that is, another table that is linked
to the rtree virtual table by an object ID) holds much more selective (and
indexed) criteria.

This is just out of the top of my head; anyway, if you don't hear back from
Thomas, please let me know, and I'll gladly provide more details (not
before Wednesday though).

And thanks again for then LEFT JOIN VIEW optimization - sorry that it
caused so much trouble ... I was completely unaware of the many pitfalls
this topic involves.

Wolfgang

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: SELECT WHERE with RTREE and second condition slow

tf@qvgps.com
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 schrieb Richard Hipp:
>
>> On 5/27/17, Thomas Flemming <[hidden email]> 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 < 30.46203 AND  x1 > 30.00074766
>>>                                     AND   18 BETWEEN z0 AND z1
>>>                                     AND   Pois_bb.Id = Pois.Id
>>> Thats very fast, 50ms.
>>>
>>> The problem is, when I add a second condition to get certain poi-types only
>>> in the area:
>>>
>>> AND styleid IN 1351,1362,1371,1374,1376,1542,1595,1597,1643,1762
>>>
>>> The query becomes really slow, 800ms.
>>> There is of course also an index on styleid.
>>>
>>> I also realized, just this query:
>>>
>>> SELECT * FROM Pois WHERE styleid IN
>>> 1351,1362,1371,1374,1376,1542,1595,1597,1643,1762
>>>
>>> is also slow for the first call. The second call is fast.
>>>
>>> (Using SQLite Expert Professional 3.5 for testing).
>>>
>>> Any ideas, how to speed this up?
>>
>> Have you tried running ANALYZE on your database?
>
> Does ANALYZE gather statistical data about rtree virtual tables? I seem to
> remember that this is not the case.
>
> I don't have an appropriate database at hand right now since I'm busy
> currently with a different project, but I seem to remember that I observed
> something similar: queries with an rtree table involved seem to *always*
> prefer the spatial index over any other index, even if the clipping
> embraces the complete extent of graphical data. This is unefficient
> especially when the "related table" (that is, another table that is linked
> to the rtree virtual table by an object ID) holds much more selective (and
> indexed) criteria.
>
> This is just out of the top of my head; anyway, if you don't hear back from
> Thomas, please let me know, and I'll gladly provide more details (not
> before Wednesday though).
>
> And thanks again for then LEFT JOIN VIEW optimization - sorry that it
> caused so much trouble ... I was completely unaware of the many pitfalls
> this topic involves.
>
> Wolfgang
>
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>

--
/****************************************
**   Flemming Software Development CC
**   Thomas Flemming
**   PO Box 81244
**   Windhoek, Namibia
**   http://www.quovadis-gps.com
**   mail  [hidden email]
**   +264 (0)81 3329923
**   +49  (0)6182 8492599
***************************************/
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: SELECT WHERE with RTREE and second condition slow

tf@qvgps.com
In reply to this post by tf@qvgps.com
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, 77 records, 49ms:
SELECT Pois.* FROM Pois_bb, Pois WHERE y0 < -14.8600 AND  y1 > -15.12862
                                    AND x0 < 30.46203 AND  x1 > 30.00074766
                                    AND 18 BETWEEN z0 AND z1
                                    AND Pois_bb.Id = Pois.Id;


fast, 1.5 mio records, 600ms, (sometimes very fast 60ms??):
SELECT styleid FROM Pois WHERE styleid IN (9,48,73,200,142,31,219);


very slow: 55 records, 30000ms:
SELECT Pois.* FROM Pois_bb, Pois WHERE y0 < -14.8600 AND  y1 > -15.12862
                                    AND x0 < 30.46203 AND  x1 > 30.00074766
                                    AND 18 BETWEEN z0 AND z1
                                    AND Pois_bb.Id = Pois.Id
                                    AND styleid IN (9,48,73,200,142,31,219);

Thanks,
Tom

Am 27.05.2017 um 12:04 schrieb Thomas Flemming:

> 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
>                                    AND   18 BETWEEN z0 AND z1
>                                    AND   Pois_bb.Id = Pois.Id
> Thats very fast, 50ms.
>
> The problem is, when I add a second condition to get certain poi-types only in
> the area:
>
> AND styleid IN 1351,1362,1371,1374,1376,1542,1595,1597,1643,1762
>
> The query becomes really slow, 800ms.
> There is of course also an index on styleid.
>
> I also realized, just this query:
>
> SELECT * FROM Pois WHERE styleid IN
> 1351,1362,1371,1374,1376,1542,1595,1597,1643,1762
>
> is also slow for the first call. The second call is fast.
>
> (Using SQLite Expert Professional 3.5 for testing).
>
> Any ideas, how to speed this up?
>
> Thanks
> Tom
>
>

--
/****************************************
**   Flemming Software Development CC
**   Thomas Flemming
**   PO Box 81244
**   Windhoek, Namibia
**   http://www.quovadis-gps.com
**   mail  [hidden email]
**   +264 (0)81 3329923
**   +49  (0)6182 8492599
***************************************/
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: SELECT WHERE with RTREE and second condition slow

Wolfgang Enzinger
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 KEY

to

INTEGER PRIMARY KEY

This can make a huge difference AFAIK.

> 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, 77 records, 49ms:
> SELECT Pois.* FROM Pois_bb, Pois WHERE y0 < -14.8600 AND  y1 > -15.12862
>                                     AND x0 < 30.46203 AND  x1 > 30.00074766
>                                     AND 18 BETWEEN z0 AND z1
>                                     AND Pois_bb.Id = Pois.Id;
>
>
> fast, 1.5 mio records, 600ms, (sometimes very fast 60ms??):
> SELECT styleid FROM Pois WHERE styleid IN (9,48,73,200,142,31,219);
>
>
> very slow: 55 records, 30000ms:
> SELECT Pois.* FROM Pois_bb, Pois WHERE y0 < -14.8600 AND  y1 > -15.12862
>                                     AND x0 < 30.46203 AND  x1 > 30.00074766
>                                     AND 18 BETWEEN z0 AND z1
>                                     AND Pois_bb.Id = Pois.Id
>                                     AND styleid IN (9,48,73,200,142,31,219);

Do you know which SQLite version is being used by SQLite Expert
Professional 3.5? Just wondering because my quick tests on this reveal
different query plans, depending on the presence of a sqlite_stat4 table
(absent in your database) and the SQLite version (I ran some quick tests
using SQLite 3.13 and 3.18). I'll have to dig deeper into this in the next
days though.

Wolfgang

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: SELECT WHERE with RTREE and second condition slow

tf@qvgps.com
 > 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 be LONG because the source for this column is really
containing 64-bit values (osm-ids).

Tom


Am 30.05.2017 um 13:29 schrieb Wolfgang Enzinger:

> 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 KEY
>
> to
>
> INTEGER PRIMARY KEY
>
> This can make a huge difference AFAIK.
>
>> 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, 77 records, 49ms:
>> SELECT Pois.* FROM Pois_bb, Pois WHERE y0 < -14.8600 AND  y1 > -15.12862
>>                                      AND x0 < 30.46203 AND  x1 > 30.00074766
>>                                      AND 18 BETWEEN z0 AND z1
>>                                      AND Pois_bb.Id = Pois.Id;
>>
>>
>> fast, 1.5 mio records, 600ms, (sometimes very fast 60ms??):
>> SELECT styleid FROM Pois WHERE styleid IN (9,48,73,200,142,31,219);
>>
>>
>> very slow: 55 records, 30000ms:
>> SELECT Pois.* FROM Pois_bb, Pois WHERE y0 < -14.8600 AND  y1 > -15.12862
>>                                      AND x0 < 30.46203 AND  x1 > 30.00074766
>>                                      AND 18 BETWEEN z0 AND z1
>>                                      AND Pois_bb.Id = Pois.Id
>>                                      AND styleid IN (9,48,73,200,142,31,219);
>
> Do you know which SQLite version is being used by SQLite Expert
> Professional 3.5? Just wondering because my quick tests on this reveal
> different query plans, depending on the presence of a sqlite_stat4 table
> (absent in your database) and the SQLite version (I ran some quick tests
> using SQLite 3.13 and 3.18). I'll have to dig deeper into this in the next
> days though.
>
> Wolfgang
>
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>

--
/****************************************
**   Flemming Software Development CC
**   Thomas Flemming
**   PO Box 81244
**   Windhoek, Namibia
**   http://www.quovadis-gps.com
**   mail  [hidden email]
**   +264 (0)81 3329923
**   +49  (0)6182 8492599
***************************************/
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: SELECT WHERE with RTREE and second condition slow

Hick Gunter
INTEGER and LONG can both store 64 bits in SQlite. The difference is that "INTEGER PRIMARY KEY" makes the column an alias for the rowid, whereas "LONG PRIMARY KEY" defines a second, possibly redundant index.

-----Ursprüngliche Nachricht-----
Von: sqlite-users [mailto:[hidden email]] Im Auftrag von Thomas Flemming
Gesendet: Dienstag, 30. Mai 2017 16:08
An: [hidden email]
Betreff: Re: [sqlite] SELECT WHERE with RTREE and second condition slow

 > 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 be LONG because the source for this column is really containing 64-bit values (osm-ids).

Tom


Am 30.05.2017 um 13:29 schrieb Wolfgang Enzinger:

> 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 KEY
>
> to
>
> INTEGER PRIMARY KEY
>
> This can make a huge difference AFAIK.
>
>> 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, 77 records, 49ms:
>> SELECT Pois.* FROM Pois_bb, Pois WHERE y0 < -14.8600 AND  y1 > -15.12862
>>                                      AND x0 < 30.46203 AND  x1 > 30.00074766
>>                                      AND 18 BETWEEN z0 AND z1
>>                                      AND Pois_bb.Id = Pois.Id;
>>
>>
>> fast, 1.5 mio records, 600ms, (sometimes very fast 60ms??):
>> SELECT styleid FROM Pois WHERE styleid IN (9,48,73,200,142,31,219);
>>
>>
>> very slow: 55 records, 30000ms:
>> SELECT Pois.* FROM Pois_bb, Pois WHERE y0 < -14.8600 AND  y1 > -15.12862
>>                                      AND x0 < 30.46203 AND  x1 > 30.00074766
>>                                      AND 18 BETWEEN z0 AND z1
>>                                      AND Pois_bb.Id = Pois.Id
>>                                      AND styleid IN
>> (9,48,73,200,142,31,219);
>
> Do you know which SQLite version is being used by SQLite Expert
> Professional 3.5? Just wondering because my quick tests on this reveal
> different query plans, depending on the presence of a sqlite_stat4
> table (absent in your database) and the SQLite version (I ran some
> quick tests using SQLite 3.13 and 3.18). I'll have to dig deeper into
> this in the next days though.
>
> Wolfgang
>
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>

--
/****************************************
**   Flemming Software Development CC
**   Thomas Flemming
**   PO Box 81244
**   Windhoek, Namibia
**   http://www.quovadis-gps.com
**   mail  [hidden email]
**   +264 (0)81 3329923
**   +49  (0)6182 8492599
***************************************/
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users


___________________________________________
 Gunter Hick
Software Engineer
Scientific Games International GmbH
FN 157284 a, HG Wien
Klitschgasse 2-4, A-1130 Vienna, Austria
Tel: +43 1 80100 0
E-Mail: [hidden email]

This communication (including any attachments) is intended for the use of the intended recipient(s) only and may contain information that is confidential, privileged or legally protected. Any unauthorized use or dissemination of this communication is strictly prohibited. If you have received this communication in error, please immediately notify the sender by return e-mail message and delete all copies of the original communication. Thank you for your cooperation.


_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: SELECT WHERE with RTREE and second condition slow

Joseph L. Casale
In reply to this post by tf@qvgps.com
-----Original Message-----
From: sqlite-users [mailto:[hidden email]] On
Behalf Of Thomas Flemming
Sent: Tuesday, May 30, 2017 8:08 AM
To: [hidden email]
Subject: Re: [sqlite] SELECT WHERE with RTREE and second condition slow

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

On that matter, if you place another SQLite dll in the installation folder with a
new name, it becomes available in Tools->Options->SQLite Library as an alternate
choice over the shipped version.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: SELECT WHERE with RTREE and second condition slow

J. King-3
In reply to this post by tf@qvgps.com
On May 30, 2017 10:07:45 AM EDT, Thomas Flemming <[hidden email]> 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 an 8-byte size is required, it will scale accordingly. Specifying LONG will not yield an integer affinity like specifying INTEGER would, and for primary keys is not as efficient because the column is not an alias for rowid if you specify LONG.

In short, you should always use INTEGER PRIMARY KEY if the column is whole numbers, regardless of size.
--
J. King
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: SELECT WHERE with RTREE and second condition slow

David Raymond
In reply to this post by tf@qvgps.com
It looks like it thinks that using the index on StyleId is going to be the most beneficial, as opposed to the rtree index. How it compares a normal index's stat1 to a virtual table's stat1 I don't know. In this case you can force it to go the way you want by using "cross join" to force the ordering of the join.


-----Original Message-----
From: sqlite-users [mailto:[hidden email]] On Behalf Of Thomas Flemming
Sent: Monday, May 29, 2017 9:28 AM
To: [hidden email]
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-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, 77 records, 49ms:
SELECT Pois.* FROM Pois_bb, Pois WHERE y0 < -14.8600 AND  y1 > -15.12862
                                    AND x0 < 30.46203 AND  x1 > 30.00074766
                                    AND 18 BETWEEN z0 AND z1
                                    AND Pois_bb.Id = Pois.Id;

Run Time: real 0.109 user 0.000000 sys 0.000000
selectid|order|from|detail
0|0|0|SCAN TABLE Pois_bb VIRTUAL TABLE INDEX 2:C2E3C0E1B4D5
0|1|1|SEARCH TABLE Pois USING INDEX sqlite_autoindex_Pois_1 (Id=?)


fast, 1.5 mio records, 600ms, (sometimes very fast 60ms??):
SELECT styleid FROM Pois WHERE styleid IN (9,48,73,200,142,31,219);

Run Time: real 0.094 user 0.093601 sys 0.000000
selectid|order|from|detail
0|0|0|SEARCH TABLE Pois USING COVERING INDEX Pois_StyleId (StyleId=?)
0|0|0|EXECUTE LIST SUBQUERY 1


very slow: 55 records, 30000ms:
SELECT Pois.* FROM Pois_bb, Pois WHERE y0 < -14.8600 AND  y1 > -15.12862
                                    AND x0 < 30.46203 AND  x1 > 30.00074766
                                    AND 18 BETWEEN z0 AND z1
                                    AND Pois_bb.Id = Pois.Id
                                    AND styleid IN (9,48,73,200,142,31,219);

Run Time: real 9.422 user 5.132433 sys 4.212027
selectid|order|from|detail
0|0|1|SEARCH TABLE Pois USING INDEX Pois_StyleId (StyleId=?)
0|0|0|EXECUTE LIST SUBQUERY 1
0|1|0|SCAN TABLE Pois_bb VIRTUAL TABLE INDEX 1:


Forcing the order with CROSS JOIN
SELECT Pois.* FROM Pois_bb CROSS JOIN Pois WHERE y0 < -14.8600 AND  y1 > -15.12862
                                    AND x0 < 30.46203 AND  x1 > 30.00074766
                                    AND 18 BETWEEN z0 AND z1
                                    AND Pois_bb.Id = Pois.Id
                                    AND styleid IN (9,48,73,200,142,31,219);

Run Time: real 0.078 user 0.000000 sys 0.000000
selectid|order|from|detail
0|0|0|SCAN TABLE Pois_bb VIRTUAL TABLE INDEX 2:C2E3C0E1B4D5
0|1|1|SEARCH TABLE Pois USING INDEX sqlite_autoindex_Pois_1 (Id=?)
0|0|0|EXECUTE LIST SUBQUERY 1


CREATE TABLE Pois(
  Id LONG PRIMARY KEY,
  Label VARCHAR(50),
  Info TEXT,
  Lat FLOAT,
  Lon FLOAT,
  Z FLOAT,
  Flags INT,
  StyleId INT
);
CREATE INDEX Pois_StyleId ON Pois(StyleId);
CREATE VIRTUAL TABLE Pois_bb USING rtree(
  Id LONG PRIMARY KEY,
  X0 FLOAT,
  X1 FLOAT,
  Y0 FLOAT,
  Y1 FLOAT,
  Z0 FLOAT,
  Z1 FLOAT
);
CREATE TABLE IF NOT EXISTS "Lines_bb_node"(nodeno INTEGER PRIMARY KEY, data BLOB);
CREATE TABLE IF NOT EXISTS "Lines_bb_rowid"(rowid INTEGER PRIMARY KEY, nodeno INTEGER);
CREATE TABLE IF NOT EXISTS "Lines_bb_parent"(nodeno INTEGER PRIMARY KEY, parentnode INTEGER);

sqlite_stat1
tbl|idx|stat
Pois|Pois_StyleId|11421177 16996
Pois|sqlite_autoindex_Pois_1|11421177 1
Pois_bb_rowid||11421177
Pois_bb_node||611106
Pois_bb_parent||611105
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: SELECT WHERE with RTREE and second condition slow

tf@qvgps.com
> 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 16:38 schrieb David Raymond:
> It looks like it thinks that using the index on StyleId is going to be the most beneficial, as opposed to the rtree index. How it compares a normal index's stat1 to a virtual table's stat1 I don't know. In this case you can force it to go the way you want by using "cross join" to force the ordering of
the join.

>
>
> -----Original Message-----
> From: sqlite-users [mailto:[hidden email]] On Behalf Of Thomas Flemming
> Sent: Monday, May 29, 2017 9:28 AM
> To: [hidden email]
> 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-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, 77 records, 49ms:
> SELECT Pois.* FROM Pois_bb, Pois WHERE y0 < -14.8600 AND  y1 > -15.12862
>                                      AND x0 < 30.46203 AND  x1 > 30.00074766
>                                      AND 18 BETWEEN z0 AND z1
>                                      AND Pois_bb.Id = Pois.Id;
>
> Run Time: real 0.109 user 0.000000 sys 0.000000
> selectid|order|from|detail
> 0|0|0|SCAN TABLE Pois_bb VIRTUAL TABLE INDEX 2:C2E3C0E1B4D5
> 0|1|1|SEARCH TABLE Pois USING INDEX sqlite_autoindex_Pois_1 (Id=?)
>
>
> fast, 1.5 mio records, 600ms, (sometimes very fast 60ms??):
> SELECT styleid FROM Pois WHERE styleid IN (9,48,73,200,142,31,219);
>
> Run Time: real 0.094 user 0.093601 sys 0.000000
> selectid|order|from|detail
> 0|0|0|SEARCH TABLE Pois USING COVERING INDEX Pois_StyleId (StyleId=?)
> 0|0|0|EXECUTE LIST SUBQUERY 1
>
>
> very slow: 55 records, 30000ms:
> SELECT Pois.* FROM Pois_bb, Pois WHERE y0 < -14.8600 AND  y1 > -15.12862
>                                      AND x0 < 30.46203 AND  x1 > 30.00074766
>                                      AND 18 BETWEEN z0 AND z1
>                                      AND Pois_bb.Id = Pois.Id
>                                      AND styleid IN (9,48,73,200,142,31,219);
>
> Run Time: real 9.422 user 5.132433 sys 4.212027
> selectid|order|from|detail
> 0|0|1|SEARCH TABLE Pois USING INDEX Pois_StyleId (StyleId=?)
> 0|0|0|EXECUTE LIST SUBQUERY 1
> 0|1|0|SCAN TABLE Pois_bb VIRTUAL TABLE INDEX 1:
>
>
> Forcing the order with CROSS JOIN
> SELECT Pois.* FROM Pois_bb CROSS JOIN Pois WHERE y0 < -14.8600 AND  y1 > -15.12862
>                                      AND x0 < 30.46203 AND  x1 > 30.00074766
>                                      AND 18 BETWEEN z0 AND z1
>                                      AND Pois_bb.Id = Pois.Id
>                                      AND styleid IN (9,48,73,200,142,31,219);
>
> Run Time: real 0.078 user 0.000000 sys 0.000000
> selectid|order|from|detail
> 0|0|0|SCAN TABLE Pois_bb VIRTUAL TABLE INDEX 2:C2E3C0E1B4D5
> 0|1|1|SEARCH TABLE Pois USING INDEX sqlite_autoindex_Pois_1 (Id=?)
> 0|0|0|EXECUTE LIST SUBQUERY 1
>
>
> CREATE TABLE Pois(
>    Id LONG PRIMARY KEY,
>    Label VARCHAR(50),
>    Info TEXT,
>    Lat FLOAT,
>    Lon FLOAT,
>    Z FLOAT,
>    Flags INT,
>    StyleId INT
> );
> CREATE INDEX Pois_StyleId ON Pois(StyleId);
> CREATE VIRTUAL TABLE Pois_bb USING rtree(
>    Id LONG PRIMARY KEY,
>    X0 FLOAT,
>    X1 FLOAT,
>    Y0 FLOAT,
>    Y1 FLOAT,
>    Z0 FLOAT,
>    Z1 FLOAT
> );
> CREATE TABLE IF NOT EXISTS "Lines_bb_node"(nodeno INTEGER PRIMARY KEY, data BLOB);
> CREATE TABLE IF NOT EXISTS "Lines_bb_rowid"(rowid INTEGER PRIMARY KEY, nodeno INTEGER);
> CREATE TABLE IF NOT EXISTS "Lines_bb_parent"(nodeno INTEGER PRIMARY KEY, parentnode INTEGER);
>
> sqlite_stat1
> tbl|idx|stat
> Pois|Pois_StyleId|11421177 16996
> Pois|sqlite_autoindex_Pois_1|11421177 1
> Pois_bb_rowid||11421177
> Pois_bb_node||611106
> Pois_bb_parent||611105
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>

--
/****************************************
**   Flemming Software Development CC
**   Thomas Flemming
**   PO Box 81244
**   Windhoek, Namibia
**   http://www.quovadis-gps.com
**   mail  [hidden email]
**   +264 (0)81 3329923
**   +49  (0)6182 8492599
***************************************/
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: SELECT WHERE with RTREE and second condition slow

Hick Gunter
Just like any other join, but with tables in the desired order and the word CROSS added

SELECT ... FROM <tablea> CROSS JOIN <tableb> ...

-----Ursprüngliche Nachricht-----
Von: sqlite-users [mailto:[hidden email]] Im Auftrag von Thomas Flemming
Gesendet: Dienstag, 30. Mai 2017 18:15
An: [hidden email]
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 16:38 schrieb David Raymond:
> It looks like it thinks that using the index on StyleId is going to be
> the most beneficial, as opposed to the rtree index. How it compares a
> normal index's stat1 to a virtual table's stat1 I don't know. In this
> case you can force it to go the way you want by using "cross join" to
> force the ordering of
the join.

>
>
> -----Original Message-----
> From: sqlite-users
> [mailto:[hidden email]] On Behalf Of
> Thomas Flemming
> Sent: Monday, May 29, 2017 9:28 AM
> To: [hidden email]
> 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-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, 77 records, 49ms:
> SELECT Pois.* FROM Pois_bb, Pois WHERE y0 < -14.8600 AND  y1 > -15.12862
>                                      AND x0 < 30.46203 AND  x1 > 30.00074766
>                                      AND 18 BETWEEN z0 AND z1
>                                      AND Pois_bb.Id = Pois.Id;
>
> Run Time: real 0.109 user 0.000000 sys 0.000000
> selectid|order|from|detail
> 0|0|0|SCAN TABLE Pois_bb VIRTUAL TABLE INDEX 2:C2E3C0E1B4D5
> 0|1|1|SEARCH TABLE Pois USING INDEX sqlite_autoindex_Pois_1 (Id=?)
>
>
> fast, 1.5 mio records, 600ms, (sometimes very fast 60ms??):
> SELECT styleid FROM Pois WHERE styleid IN (9,48,73,200,142,31,219);
>
> Run Time: real 0.094 user 0.093601 sys 0.000000
> selectid|order|from|detail
> 0|0|0|SEARCH TABLE Pois USING COVERING INDEX Pois_StyleId (StyleId=?)
> 0|0|0|EXECUTE LIST SUBQUERY 1
>
>
> very slow: 55 records, 30000ms:
> SELECT Pois.* FROM Pois_bb, Pois WHERE y0 < -14.8600 AND  y1 > -15.12862
>                                      AND x0 < 30.46203 AND  x1 > 30.00074766
>                                      AND 18 BETWEEN z0 AND z1
>                                      AND Pois_bb.Id = Pois.Id
>                                      AND styleid IN
> (9,48,73,200,142,31,219);
>
> Run Time: real 9.422 user 5.132433 sys 4.212027
> selectid|order|from|detail
> 0|0|1|SEARCH TABLE Pois USING INDEX Pois_StyleId (StyleId=?)
> 0|0|0|EXECUTE LIST SUBQUERY 1
> 0|1|0|SCAN TABLE Pois_bb VIRTUAL TABLE INDEX 1:
>
>
> Forcing the order with CROSS JOIN
> SELECT Pois.* FROM Pois_bb CROSS JOIN Pois WHERE y0 < -14.8600 AND  y1 > -15.12862
>                                      AND x0 < 30.46203 AND  x1 > 30.00074766
>                                      AND 18 BETWEEN z0 AND z1
>                                      AND Pois_bb.Id = Pois.Id
>                                      AND styleid IN
> (9,48,73,200,142,31,219);
>
> Run Time: real 0.078 user 0.000000 sys 0.000000
> selectid|order|from|detail
> 0|0|0|SCAN TABLE Pois_bb VIRTUAL TABLE INDEX 2:C2E3C0E1B4D5
> 0|1|1|SEARCH TABLE Pois USING INDEX sqlite_autoindex_Pois_1 (Id=?)
> 0|0|0|EXECUTE LIST SUBQUERY 1
>
>
> CREATE TABLE Pois(
>    Id LONG PRIMARY KEY,
>    Label VARCHAR(50),
>    Info TEXT,
>    Lat FLOAT,
>    Lon FLOAT,
>    Z FLOAT,
>    Flags INT,
>    StyleId INT
> );
> CREATE INDEX Pois_StyleId ON Pois(StyleId); CREATE VIRTUAL TABLE
> Pois_bb USING rtree(
>    Id LONG PRIMARY KEY,
>    X0 FLOAT,
>    X1 FLOAT,
>    Y0 FLOAT,
>    Y1 FLOAT,
>    Z0 FLOAT,
>    Z1 FLOAT
> );
> CREATE TABLE IF NOT EXISTS "Lines_bb_node"(nodeno INTEGER PRIMARY KEY,
> data BLOB); CREATE TABLE IF NOT EXISTS "Lines_bb_rowid"(rowid INTEGER
> PRIMARY KEY, nodeno INTEGER); CREATE TABLE IF NOT EXISTS
> "Lines_bb_parent"(nodeno INTEGER PRIMARY KEY, parentnode INTEGER);
>
> sqlite_stat1
> tbl|idx|stat
> Pois|Pois_StyleId|11421177 16996
> Pois|sqlite_autoindex_Pois_1|11421177 1
> Pois_bb_rowid||11421177
> Pois_bb_node||611106
> Pois_bb_parent||611105
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>

--
/****************************************
**   Flemming Software Development CC
**   Thomas Flemming
**   PO Box 81244
**   Windhoek, Namibia
**   http://www.quovadis-gps.com
**   mail  [hidden email]
**   +264 (0)81 3329923
**   +49  (0)6182 8492599
***************************************/
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users


___________________________________________
 Gunter Hick
Software Engineer
Scientific Games International GmbH
FN 157284 a, HG Wien
Klitschgasse 2-4, A-1130 Vienna, Austria
Tel: +43 1 80100 0
E-Mail: [hidden email]

This communication (including any attachments) is intended for the use of the intended recipient(s) only and may contain information that is confidential, privileged or legally protected. Any unauthorized use or dissemination of this communication is strictly prohibited. If you have received this communication in error, please immediately notify the sender by return e-mail message and delete all copies of the original communication. Thank you for your cooperation.


_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: SELECT WHERE with RTREE and second condition slow

tf@qvgps.com
but the query is not in two tables.
its two select-where in the same table.

Am 30.05.2017 um 17:18 schrieb Hick Gunter:

> Just like any other join, but with tables in the desired order and the word CROSS added
>
> SELECT ... FROM <tablea> CROSS JOIN <tableb> ...
>
> -----Ursprüngliche Nachricht-----
> Von: sqlite-users [mailto:[hidden email]] Im Auftrag von Thomas Flemming
> Gesendet: Dienstag, 30. Mai 2017 18:15
> An: [hidden email]
> 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 16:38 schrieb David Raymond:
>> It looks like it thinks that using the index on StyleId is going to be
>> the most beneficial, as opposed to the rtree index. How it compares a
>> normal index's stat1 to a virtual table's stat1 I don't know. In this
>> case you can force it to go the way you want by using "cross join" to
>> force the ordering of
> the join.
>>
>>
>> -----Original Message-----
>> From: sqlite-users
>> [mailto:[hidden email]] On Behalf Of
>> Thomas Flemming
>> Sent: Monday, May 29, 2017 9:28 AM
>> To: [hidden email]
>> 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-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, 77 records, 49ms:
>> SELECT Pois.* FROM Pois_bb, Pois WHERE y0 < -14.8600 AND  y1 > -15.12862
>>                                       AND x0 < 30.46203 AND  x1 > 30.00074766
>>                                       AND 18 BETWEEN z0 AND z1
>>                                       AND Pois_bb.Id = Pois.Id;
>>
>> Run Time: real 0.109 user 0.000000 sys 0.000000
>> selectid|order|from|detail
>> 0|0|0|SCAN TABLE Pois_bb VIRTUAL TABLE INDEX 2:C2E3C0E1B4D5
>> 0|1|1|SEARCH TABLE Pois USING INDEX sqlite_autoindex_Pois_1 (Id=?)
>>
>>
>> fast, 1.5 mio records, 600ms, (sometimes very fast 60ms??):
>> SELECT styleid FROM Pois WHERE styleid IN (9,48,73,200,142,31,219);
>>
>> Run Time: real 0.094 user 0.093601 sys 0.000000
>> selectid|order|from|detail
>> 0|0|0|SEARCH TABLE Pois USING COVERING INDEX Pois_StyleId (StyleId=?)
>> 0|0|0|EXECUTE LIST SUBQUERY 1
>>
>>
>> very slow: 55 records, 30000ms:
>> SELECT Pois.* FROM Pois_bb, Pois WHERE y0 < -14.8600 AND  y1 > -15.12862
>>                                       AND x0 < 30.46203 AND  x1 > 30.00074766
>>                                       AND 18 BETWEEN z0 AND z1
>>                                       AND Pois_bb.Id = Pois.Id
>>                                       AND styleid IN
>> (9,48,73,200,142,31,219);
>>
>> Run Time: real 9.422 user 5.132433 sys 4.212027
>> selectid|order|from|detail
>> 0|0|1|SEARCH TABLE Pois USING INDEX Pois_StyleId (StyleId=?)
>> 0|0|0|EXECUTE LIST SUBQUERY 1
>> 0|1|0|SCAN TABLE Pois_bb VIRTUAL TABLE INDEX 1:
>>
>>
>> Forcing the order with CROSS JOIN
>> SELECT Pois.* FROM Pois_bb CROSS JOIN Pois WHERE y0 < -14.8600 AND  y1 > -15.12862
>>                                       AND x0 < 30.46203 AND  x1 > 30.00074766
>>                                       AND 18 BETWEEN z0 AND z1
>>                                       AND Pois_bb.Id = Pois.Id
>>                                       AND styleid IN
>> (9,48,73,200,142,31,219);
>>
>> Run Time: real 0.078 user 0.000000 sys 0.000000
>> selectid|order|from|detail
>> 0|0|0|SCAN TABLE Pois_bb VIRTUAL TABLE INDEX 2:C2E3C0E1B4D5
>> 0|1|1|SEARCH TABLE Pois USING INDEX sqlite_autoindex_Pois_1 (Id=?)
>> 0|0|0|EXECUTE LIST SUBQUERY 1
>>
>>
>> CREATE TABLE Pois(
>>     Id LONG PRIMARY KEY,
>>     Label VARCHAR(50),
>>     Info TEXT,
>>     Lat FLOAT,
>>     Lon FLOAT,
>>     Z FLOAT,
>>     Flags INT,
>>     StyleId INT
>> );
>> CREATE INDEX Pois_StyleId ON Pois(StyleId); CREATE VIRTUAL TABLE
>> Pois_bb USING rtree(
>>     Id LONG PRIMARY KEY,
>>     X0 FLOAT,
>>     X1 FLOAT,
>>     Y0 FLOAT,
>>     Y1 FLOAT,
>>     Z0 FLOAT,
>>     Z1 FLOAT
>> );
>> CREATE TABLE IF NOT EXISTS "Lines_bb_node"(nodeno INTEGER PRIMARY KEY,
>> data BLOB); CREATE TABLE IF NOT EXISTS "Lines_bb_rowid"(rowid INTEGER
>> PRIMARY KEY, nodeno INTEGER); CREATE TABLE IF NOT EXISTS
>> "Lines_bb_parent"(nodeno INTEGER PRIMARY KEY, parentnode INTEGER);
>>
>> sqlite_stat1
>> tbl|idx|stat
>> Pois|Pois_StyleId|11421177 16996
>> Pois|sqlite_autoindex_Pois_1|11421177 1
>> Pois_bb_rowid||11421177
>> Pois_bb_node||611106
>> Pois_bb_parent||611105
>> _______________________________________________
>> sqlite-users mailing list
>> [hidden email]
>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>>
>
> --
> /****************************************
> **   Flemming Software Development CC
> **   Thomas Flemming
> **   PO Box 81244
> **   Windhoek, Namibia
> **   http://www.quovadis-gps.com
> **   mail  [hidden email]
> **   +264 (0)81 3329923
> **   +49  (0)6182 8492599
> ***************************************/
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
>
> ___________________________________________
>   Gunter Hick
> Software Engineer
> Scientific Games International GmbH
> FN 157284 a, HG Wien
> Klitschgasse 2-4, A-1130 Vienna, Austria
> Tel: +43 1 80100 0
> E-Mail: [hidden email]
>
> This communication (including any attachments) is intended for the use of the intended recipient(s) only and may contain information that is confidential, privileged or legally protected. Any unauthorized use or dissemination of this communication is strictly prohibited. If you have received this communication in error, please immediately notify the sender by return e-mail message and delete all copies of the original communication. Thank you for your cooperation.
>
>
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>

--
/****************************************
**   Flemming Software Development CC
**   Thomas Flemming
**   PO Box 81244
**   Windhoek, Namibia
**   http://www.quovadis-gps.com
**   mail  [hidden email]
**   +264 (0)81 3329923
**   +49  (0)6182 8492599
***************************************/
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: SELECT WHERE with RTREE and second condition slow

Richard Hipp-3
In reply to this post by tf@qvgps.com
On 5/27/17, Thomas Flemming <[hidden email]> 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 < 30.46203 AND  x1 > 30.00074766
>                                    AND   18 BETWEEN z0 AND z1
>                                    AND   Pois_bb.Id = Pois.Id
> Thats very fast, 50ms.
>
> The problem is, when I add a second condition to get certain poi-types only
> in the area:
>
> AND styleid IN 1351,1362,1371,1374,1376,1542,1595,1597,1643,1762

Try putting a "+" symbol before "styleid".  Like this:

   AND +styleid IN (1351,1362,1371,1374,1376,1542,1595,1597,1643,1762)


>
> The query becomes really slow, 800ms.
> There is of course also an index on styleid.
>
> I also realized, just this query:
>
> SELECT * FROM Pois WHERE styleid IN
> 1351,1362,1371,1374,1376,1542,1595,1597,1643,1762
>
> is also slow for the first call. The second call is fast.
>
> (Using SQLite Expert Professional 3.5 for testing).
>
> Any ideas, how to speed this up?
>
> Thanks
> Tom
>
>
> --
> /****************************************
> **   Flemming Software Development CC
> **   Thomas Flemming
> **   PO Box 81244
> **   Windhoek, Namibia
> **   http://www.quovadis-gps.com
> **   mail  [hidden email]
> **   +264 (0)81 3329923
> **   +49  (0)6182 8492599
> ***************************************/
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>


--
D. Richard Hipp
[hidden email]
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: SELECT WHERE with RTREE and second condition slow

tf@qvgps.com
 > 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 Flemming <[hidden email]> 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 < 30.46203 AND  x1 > 30.00074766
>>                                     AND   18 BETWEEN z0 AND z1
>>                                     AND   Pois_bb.Id = Pois.Id
>> Thats very fast, 50ms.
>>
>> The problem is, when I add a second condition to get certain poi-types only
>> in the area:
>>
>> AND styleid IN 1351,1362,1371,1374,1376,1542,1595,1597,1643,1762
>
> Try putting a "+" symbol before "styleid".  Like this:
>
>     AND +styleid IN (1351,1362,1371,1374,1376,1542,1595,1597,1643,1762)
>
>
>>
>> The query becomes really slow, 800ms.
>> There is of course also an index on styleid.
>>
>> I also realized, just this query:
>>
>> SELECT * FROM Pois WHERE styleid IN
>> 1351,1362,1371,1374,1376,1542,1595,1597,1643,1762
>>
>> is also slow for the first call. The second call is fast.
>>
>> (Using SQLite Expert Professional 3.5 for testing).
>>
>> Any ideas, how to speed this up?
>>
>> Thanks
>> Tom
>>
>>
>> --
>> /****************************************
>> **   Flemming Software Development CC
>> **   Thomas Flemming
>> **   PO Box 81244
>> **   Windhoek, Namibia
>> **   http://www.quovadis-gps.com
>> **   mail  [hidden email]
>> **   +264 (0)81 3329923
>> **   +49  (0)6182 8492599
>> ***************************************/
>> _______________________________________________
>> sqlite-users mailing list
>> [hidden email]
>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>>
>
>

--
/****************************************
**   Flemming Software Development CC
**   Thomas Flemming
**   PO Box 81244
**   Windhoek, Namibia
**   http://www.quovadis-gps.com
**   mail  [hidden email]
**   +264 (0)81 3329923
**   +49  (0)6182 8492599
***************************************/
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: SELECT WHERE with RTREE and second condition slow

David Raymond
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 that into an expression rather than the raw field in the eyes of the planner, so it doesn't use that index on StyleId.

"but the query is not in two tables.
its two select-where in the same table."
Then what is "FROM Pois_bb, Pois WHERE...Pois_bb.Id = Pois.Id"?
That's joining two tables together. The fields you wanted returned may only be from one of them, but it's still a join. The quick version(s) have Pois_bb as the outer loop and Pois as the inner loop. The slowed down version had Pois as the outer loop and Pois_bb as the inner loop.



SELECT Pois.* FROM Pois_bb, Pois WHERE y0 < -14.8600 AND  y1 > -15.12862
                                    AND x0 < 30.46203 AND  x1 > 30.00074766
                                    AND 18 BETWEEN z0 AND z1
                                    AND Pois_bb.Id = Pois.Id;

Run Time: real 0.109 user 0.000000 sys 0.000000
selectid|order|from|detail
0|0|0|SCAN TABLE Pois_bb VIRTUAL TABLE INDEX 2:C2E3C0E1B4D5
0|1|1|SEARCH TABLE Pois USING INDEX sqlite_autoindex_Pois_1 (Id=?)


SELECT Pois.* FROM Pois_bb, Pois WHERE y0 < -14.8600 AND  y1 > -15.12862
                                    AND x0 < 30.46203 AND  x1 > 30.00074766
                                    AND 18 BETWEEN z0 AND z1
                                    AND Pois_bb.Id = Pois.Id
                                    AND styleid IN (9,48,73,200,142,31,219);

Run Time: real 9.422 user 5.132433 sys 4.212027
selectid|order|from|detail
0|0|1|SEARCH TABLE Pois USING INDEX Pois_StyleId (StyleId=?)
0|0|0|EXECUTE LIST SUBQUERY 1
0|1|0|SCAN TABLE Pois_bb VIRTUAL TABLE INDEX 1:


Forcing the order with CROSS JOIN
SELECT Pois.* FROM Pois_bb CROSS JOIN Pois WHERE y0 < -14.8600 AND  y1 > -15.12862
                                    AND x0 < 30.46203 AND  x1 > 30.00074766
                                    AND 18 BETWEEN z0 AND z1
                                    AND Pois_bb.Id = Pois.Id
                                    AND styleid IN (9,48,73,200,142,31,219);

Run Time: real 0.078 user 0.000000 sys 0.000000
selectid|order|from|detail
0|0|0|SCAN TABLE Pois_bb VIRTUAL TABLE INDEX 2:C2E3C0E1B4D5
0|1|1|SEARCH TABLE Pois USING INDEX sqlite_autoindex_Pois_1 (Id=?)
0|0|0|EXECUTE LIST SUBQUERY 1


With the unary + operator
SELECT Pois.* FROM Pois_bb CROSS JOIN Pois WHERE y0 < -14.8600 AND  y1 > -15.12862
                                    AND x0 < 30.46203 AND  x1 > 30.00074766
                                    AND 18 BETWEEN z0 AND z1
                                    AND Pois_bb.Id = Pois.Id
                                    AND +styleid IN (9,48,73,200,142,31,219);
selectid|order|from|detail
0|0|0|SCAN TABLE Pois_bb VIRTUAL TABLE INDEX 2:C2E3C0E1B4D5
0|1|1|SEARCH TABLE Pois USING INDEX sqlite_autoindex_Pois_1 (Id=?)
0|0|0|EXECUTE LIST SUBQUERY 1
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: SELECT WHERE with RTREE and second condition slow

E.Pasma
In reply to this post by tf@qvgps.com
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 explained here:
http://sqlite.org/optoverview.html#uplus
(a most interesting document anyway)
Regards, E.  Pasma

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: SELECT WHERE with RTREE and second condition slow

tf@qvgps.com
In reply to this post by David Raymond
 > 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 Raymond:

> 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 that into an expression rather than the raw field in the eyes of the planner, so it doesn't use that index on StyleId.
>
> "but the query is not in two tables.
> its two select-where in the same table."
> Then what is "FROM Pois_bb, Pois WHERE...Pois_bb.Id = Pois.Id"?
> That's joining two tables together. The fields you wanted returned may only be from one of them, but it's still a join. The quick version(s) have Pois_bb as the outer loop and Pois as the inner loop. The slowed down version had Pois as the outer loop and Pois_bb as the inner loop.
>
>
>
> SELECT Pois.* FROM Pois_bb, Pois WHERE y0 < -14.8600 AND  y1 > -15.12862
>                                      AND x0 < 30.46203 AND  x1 > 30.00074766
>                                      AND 18 BETWEEN z0 AND z1
>                                      AND Pois_bb.Id = Pois.Id;
>
> Run Time: real 0.109 user 0.000000 sys 0.000000
> selectid|order|from|detail
> 0|0|0|SCAN TABLE Pois_bb VIRTUAL TABLE INDEX 2:C2E3C0E1B4D5
> 0|1|1|SEARCH TABLE Pois USING INDEX sqlite_autoindex_Pois_1 (Id=?)
>
>
> SELECT Pois.* FROM Pois_bb, Pois WHERE y0 < -14.8600 AND  y1 > -15.12862
>                                      AND x0 < 30.46203 AND  x1 > 30.00074766
>                                      AND 18 BETWEEN z0 AND z1
>                                      AND Pois_bb.Id = Pois.Id
>                                      AND styleid IN (9,48,73,200,142,31,219);
>
> Run Time: real 9.422 user 5.132433 sys 4.212027
> selectid|order|from|detail
> 0|0|1|SEARCH TABLE Pois USING INDEX Pois_StyleId (StyleId=?)
> 0|0|0|EXECUTE LIST SUBQUERY 1
> 0|1|0|SCAN TABLE Pois_bb VIRTUAL TABLE INDEX 1:
>
>
> Forcing the order with CROSS JOIN
> SELECT Pois.* FROM Pois_bb CROSS JOIN Pois WHERE y0 < -14.8600 AND  y1 > -15.12862
>                                      AND x0 < 30.46203 AND  x1 > 30.00074766
>                                      AND 18 BETWEEN z0 AND z1
>                                      AND Pois_bb.Id = Pois.Id
>                                      AND styleid IN (9,48,73,200,142,31,219);
>
> Run Time: real 0.078 user 0.000000 sys 0.000000
> selectid|order|from|detail
> 0|0|0|SCAN TABLE Pois_bb VIRTUAL TABLE INDEX 2:C2E3C0E1B4D5
> 0|1|1|SEARCH TABLE Pois USING INDEX sqlite_autoindex_Pois_1 (Id=?)
> 0|0|0|EXECUTE LIST SUBQUERY 1
>
>
> With the unary + operator
> SELECT Pois.* FROM Pois_bb CROSS JOIN Pois WHERE y0 < -14.8600 AND  y1 > -15.12862
>                                      AND x0 < 30.46203 AND  x1 > 30.00074766
>                                      AND 18 BETWEEN z0 AND z1
>                                      AND Pois_bb.Id = Pois.Id
>                                      AND +styleid IN (9,48,73,200,142,31,219);
> selectid|order|from|detail
> 0|0|0|SCAN TABLE Pois_bb VIRTUAL TABLE INDEX 2:C2E3C0E1B4D5
> 0|1|1|SEARCH TABLE Pois USING INDEX sqlite_autoindex_Pois_1 (Id=?)
> 0|0|0|EXECUTE LIST SUBQUERY 1
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>

--
/****************************************
**   Flemming Software Development CC
**   Thomas Flemming
**   PO Box 81244
**   Windhoek, Namibia
**   http://www.quovadis-gps.com
**   mail  [hidden email]
**   +264 (0)81 3329923
**   +49  (0)6182 8492599
***************************************/
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users