I am trying to learn EXPLAIN. I have a table like so with ~184K rows sqlite> .s CREATE TABLE sg_rivers ( ogc_fid INTEGER PRIMARY KEY, wkt_geometry TEXT, name TEXT, xmin REAL, ymin REAL, xmax REAL, ymax REAL ); CREATE INDEX ix_xmax__sg_rivers ON sg_rivers (xmax); CREATE INDEX ix_xmin__sg_rivers ON sg_rivers (xmin); CREATE INDEX ix_ymax__sg_rivers ON sg_rivers (ymax); CREATE INDEX ix_ymin__sg_rivers ON sg_rivers (ymin); sqlite> select xmin, ymin, xmax, ymax from sg_rivers limit 3; xmin ymin xmax ymax ---- ------------- ---- ---- -89. 43.642034 -89. 43.6 -89. 43.642501 -89. 43.6 -89. 43.642991 -89. 43.6 sqlite> explain select wkt_geometry from sg_rivers where xmin >= -90; addr opcode p1 p2 p3 p4 p5 comment ---- ------------- ---- ---- ---- ------------- -- ------------- 0 Trace 0 0 0 explain select wkt_geometry from sg_rivers where xmin >= -90; 00 1 Goto 0 20 0 00 2 OpenRead 0 100943 0 00 3 SetNumColumns 0 4 0 00 4 OpenRead 1 197485 0 keyinfo(1,BINARY) 00 5 SetNumColumns 1 2 0 00 6 Integer -90 2 0 00 7 IsNull 2 17 0 00 8 MakeRecord 2 1 5 eb 00 9 MoveGe 1 17 5 00 10 Column 1 0 5 00 11 IsNull 5 16 0 00 12 IdxRowid 1 5 0 00 13 MoveGe 0 0 5 00 14 Column 0 1 6 00 15 ResultRow 6 1 0 00 16 Next 1 10 0 00 17 Close 0 0 0 00 18 Close 1 0 0 00 19 Halt 0 0 0 00 20 Transaction 0 0 0 00 21 VerifyCookie 0 47 0 00 22 TableLock 0 100943 0 sg_rivers 00 23 Goto 0 2 0 00
Am I to understand from the above that my query is *not* using the index ix_xmin__sg_rivers? If not, why not? In any case, would I be better off with a compound index if I modify my query to select wkt_geometry from sg_rivers where xmin >= ? and ymin >= ? and xmax <= ? and ymax <= ?; -- Puneet Kishor _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
