On Apr 18, 2008, at 9:37 AM, P Kishor wrote: > 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?
I think it is. Instruction 4 opens the index. Instruction 9 seeks to the first entry in the index where (xmin>=-90). Instructions 10-15 return you a row and instruction 16 advances to the next index entry. Try EXPLAIN QUERY PLAN for output that is easier to read. > 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 <= ?; No. The B-Tree structures that SQLite uses are not really suitable for this kind of thing. Ideally you need an R-Tree structure. In SQLite, only one of the four binary constraints in the WHERE clause could be optimized using an index. Dan. _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
