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 <t...@qvgps.com> 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
sqlite-users@mailinglists.sqlite.org
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 t...@qvgps.com
** +264 (0)81 3329923
** +49 (0)6182 8492599
***************************************/
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
