I have created an RTree table with the command CREATE VIRTUAL TABLE AirspaceRTree USING rtree (ID,MinLatitude,MaxLatitude,MinLongitude,MaxLongitude,MinAltitude,MaxAltitude);
Furthermore, I have a table Airspace. The contents besides the ID (primary key) are irrelevant for this context. When using the query below no index seems to be used SELECT Airspace.ID FROM Airspace,AirspaceRTree WHERE (Airspace.ID = AirspaceRTree.ID) AND (AirspaceRTree.MinLatitude > = 1.0); addr opcode p1 p2 p3 p4 p5 comment ---- ------------- ---- ---- ---- ------------- -- ------------- 0 Trace 0 0 0 00 1 Real 0 1 0 2 00 2 Goto 0 18 0 00 3 VOpen 1 0 0 vtab:10081EE08:10009A9E0 00 4 OpenRead 0 98 0 0 00 5 SCopy 1 4 0 00 6 Integer 2 2 0 00 7 Integer 1 3 0 00 8 VFilter 1 15 2 Db 00 9 Rowid 1 6 0 00 10 MustBeInt 6 14 0 00 11 NotExists 0 14 6 00 12 Rowid 0 7 0 00 13 ResultRow 7 1 0 00 14 VNext 1 9 0 00 15 Close 1 0 0 00 16 Close 0 0 0 00 17 Halt 0 0 0 00 18 Transaction 0 0 0 00 19 VerifyCookie 0 75 0 00 20 TableLock 0 98 0 Airspace 00 21 Goto 0 3 0 00 Changing the query to SELECT Airspace.ID FROM Airspace WHERE Airspace.ID IN (SELECT rowid FROM AirspaceRTree WHERE AirspaceRTree.MaxLatitude >= 2.0); leads to addr opcode p1 p2 p3 p4 p5 comment ---- ------------- ---- ---- ---- ------------- -- ------------- 0 Trace 0 0 0 00 1 Goto 0 27 0 00 2 OpenRead 0 98 0 0 00 3 If 2 17 0 00 4 Integer 1 2 0 00 5 OpenEphemeral 3 1 0 keyinfo(1,nil) 00 6 Real 0 3 0 2 00 7 VOpen 1 0 0 vtab:10081EE08:10009A9E0 00 8 SCopy 3 6 0 00 9 Integer 2 4 0 00 10 Integer 1 5 0 00 11 VFilter 1 16 4 Db 00 12 Rowid 1 7 0 00 13 MakeRecord 7 1 8 c 00 14 IdxInsert 3 8 0 00 15 VNext 1 12 0 00 16 Close 1 0 0 00 17 Rewind 3 25 0 00 18 Column 3 0 1 00 19 IsNull 1 24 0 00 20 MustBeInt 1 24 0 00 21 NotExists 0 24 1 00 22 Rowid 0 9 0 00 23 ResultRow 9 1 0 00 24 Next 3 18 0 00 25 Close 0 0 0 00 26 Halt 0 0 0 00 27 Transaction 0 0 0 00 28 VerifyCookie 0 75 0 00 29 TableLock 0 98 0 Airspace 00 30 Goto 0 2 0 00 Here at least on Airspace an index is used. But is the second version the fastest possible query? Hartwig _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
