Re: [sqlite] SELECT WHERE with RTREE and second condition slow

[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)
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  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