2011/10/14 Dan Kennedy <danielk1...@gmail.com> > > Good question. Can you enter the following commands into > the shell tool and post the complete output (no "QUERY PLAN" > this time): > > .version > .schema > .explain > > EXPLAIN SELECT * FROM table1 WHERE data=10 ORDER BY rowid LIMIT 250 OFFSET > 500000; > > EXPLAIN SELECT * FROM table1 WHERE data=10 LIMIT 250 OFFSET 500000; > > sqlite> .version SQLite 3.7.8 2011-09-19 14:49:19 3e0da808d2f5b4d12046e05980ca04578f581177 sqlite> .schema CREATE TABLE table1(id INTEGER PRIMARY KEY, data INT); CREATE INDEX ididx ON table1(data); sqlite> .explain sqlite>
EXPLAIN SELECT * FROM table1 WHERE data=10 ORDER BY rowid LIMIT 250 OFFSET 500000; addr opcode p1 p2 p3 p4 p5 comment ---- ------------- ---- ---- ---- ------------- -- ------------- 0 Trace 0 0 0 00 1 Noop 0 0 0 00 2 Integer 250 1 0 00 3 Integer 50000 2 0 00 4 MustBeInt 2 0 0 00 5 IfPos 2 7 0 00 6 Integer 0 2 0 00 7 Add 1 2 3 00 8 IfPos 1 10 0 00 9 Integer -1 3 0 00 10 Integer 1 4 0 00 11 Goto 0 36 0 00 12 OpenRead 0 2 0 12 00 13 Rewind 0 34 0 00 14 Column 0 1 5 00 15 Ne 4 33 5 collseq(BINARY) 6c 16 AddImm 2 -1 0 00 17 IfNeg 2 19 0 00 18 Goto 0 33 0 00 19 Rowid 0 7 0 00 20 Column 0 1 8 00 21 Column 0 2 9 00 22 Column 0 3 10 00 23 Column 0 4 11 00 24 Column 0 5 12 00 25 Column 0 6 13 00 26 Column 0 7 14 00 27 Column 0 8 15 00 28 Column 0 9 16 00 29 Column 0 10 17 00 30 Column 0 11 18 00 31 ResultRow 7 12 0 00 32 IfZero 1 34 -1 00 33 Next 0 14 0 01 34 Close 0 0 0 00 35 Halt 0 0 0 00 36 Transaction 0 0 0 00 37 VerifyCookie 0 10 0 00 38 TableLock 0 2 0 table1 00 39 Goto 0 12 0 00 EXPLAIN SELECT * FROM table1 WHERE data=10 LIMIT 250 OFFSET 500000; addr opcode p1 p2 p3 p4 p5 comment ---- ------------- ---- ---- ---- ------------- -- ------- 0 Trace 0 0 0 00 1 Integer 250 1 0 00 2 Integer 50000 2 0 00 3 MustBeInt 2 0 0 00 4 IfPos 2 6 0 00 5 Integer 0 2 0 00 6 Add 1 2 3 00 7 IfPos 1 9 0 00 8 Integer -1 3 0 00 9 Integer 1 4 0 00 10 Goto 0 38 0 00 11 OpenRead 0 2 0 12 00 12 OpenRead 1 123405 0 keyinfo(1,BINARY) 00 13 SeekGe 1 35 4 1 00 14 IdxGE 1 35 4 1 01 15 IdxRowid 1 5 0 00 16 Seek 0 5 0 00 17 AddImm 2 -1 0 00 18 IfNeg 2 20 0 00 19 Goto 0 34 0 00 20 IdxRowid 1 6 0 00 21 Column 1 0 7 00 22 Column 0 2 8 00 23 Column 0 3 9 00 24 Column 0 4 10 00 25 Column 0 5 11 00 26 Column 0 6 12 00 27 Column 0 7 13 00 28 Column 0 8 14 00 29 Column 0 9 15 00 30 Column 0 10 16 00 31 Column 0 11 17 00 32 ResultRow 6 12 0 00 33 IfZero 1 35 -1 00 34 Next 1 14 0 00 35 Close 0 0 0 00 36 Close 1 0 0 00 37 Halt 0 0 0 00 38 Transaction 0 0 0 00 39 VerifyCookie 0 10 0 00 40 TableLock 0 2 0 table1 00 41 Goto 0 11 0 00 _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
