Re: [sqlite] Understanding EXPLAIN QUERY
On 01/17/2011 06:36 AM, Sam Carleton wrote: > I am trying to optimize a query by using the EXPLAIN QUERY, but the > documentation on the web (http://www.sqlite.org/eqp.html) does not match the > version of SQLite I am using (v3.6.23.1). The documentation says there are > three columns, but I am only seeing two columns. What do the two columns > mean? Columns 0 and 1 of the old output are the same as columns 1 and 2 of the new output. Dan. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Understanding EXPLAIN QUERY
I am trying to optimize a query by using the EXPLAIN QUERY, but the documentation on the web (http://www.sqlite.org/eqp.html) does not match the version of SQLite I am using (v3.6.23.1). The documentation says there are three columns, but I am only seeing two columns. What do the two columns mean? Here is my results: sqlite> EXPLAIN QUERY PLAN ...> SELECT DISTINCT ...>f1.FolderId, ...>f1.ImageId, ...>(SELECT MIN(f2.instertedon) FROM Favorite f2 WHERE f2.FolderId = f1.FolderId AND f2.ImageId = f1.ImageId) timeMarker ...> FROM Customer c ...>JOIN Favorite f1 ON c.CustomerId = f1.CustomerId ...> WHERE c.IsInSlideShow = 1 ...>AND f1.Selected = 1 ...>AND timeMarker > julianday(0.0) ...> ORDER BY timeMarker; 0|0|TABLE Customer AS c WITH INDEX Customer_IsInSlideShow_index 1|1|TABLE Favorite AS f1 WITH INDEX Favorite_Selected_index 0|0|TABLE Favorite AS f2 WITH INDEX Favorite_FolderId_ImageId_instertedon_index ORDER BY 0|0|TABLE Favorite AS f2 WITH INDEX Favorite_FolderId_ImageId_instertedon_index ORDER BY 0|0|TABLE Favorite AS f2 WITH INDEX Favorite_FolderId_ImageId_instertedon_index ORDER BY 0|0|TABLE Favorite AS f2 WITH INDEX Favorite_FolderId_ImageId_instertedon_index ORDER BY ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] understanding EXPLAIN
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 p1p2p3p4 p5 comment > - - -- > - > 0 Trace 0 0 0 explain select wkt_geometry > from sg_rivers where xmin >= -90; 00 > 1 Goto 0 20000 > 2 OpenRead 0 100943 000 > 3 SetNumColumns 0 4 000 > 4 OpenRead 1 197485 0 keyinfo(1,BINARY) 00 > 5 SetNumColumns 1 2 000 > 6 Integer-90 2 000 > 7 IsNull 2 17000 > 8 MakeRecord 2 1 5 eb 00 > 9 MoveGe 1 17500 > 10Column 1 0 500 > 11IsNull 5 16000 > 12IdxRowid 1 5 000 > 13MoveGe 0 0 500 > 14Column 0 1 600 > 15ResultRow 6 1 000 > 16Next 1 10000 > 17Close 0 0 000 > 18Close 1 0 000 > 19Halt 0 0 000 > 20Transaction0 0 000 > 21VerifyCookie 0 47000 > 22TableLock 0 100943 0 sg_rivers 00 > 23Goto 0 2 000 > > 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
[sqlite] understanding EXPLAIN
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 p1p2p3p4 p5 comment - - -- - 0 Trace 0 0 0 explain select wkt_geometry from sg_rivers where xmin >= -90; 00 1 Goto 0 20000 2 OpenRead 0 100943 000 3 SetNumColumns 0 4 000 4 OpenRead 1 197485 0 keyinfo(1,BINARY) 00 5 SetNumColumns 1 2 000 6 Integer-90 2 000 7 IsNull 2 17000 8 MakeRecord 2 1 5 eb 00 9 MoveGe 1 17500 10Column 1 0 500 11IsNull 5 16000 12IdxRowid 1 5 000 13MoveGe 0 0 500 14Column 0 1 600 15ResultRow 6 1 000 16Next 1 10000 17Close 0 0 000 18Close 1 0 000 19Halt 0 0 000 20Transaction0 0 000 21VerifyCookie 0 47000 22TableLock 0 100943 0 sg_rivers 00 23Goto 0 2 000 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
