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
