Un-analyzed here's what I'm getting while looking at the db: With distinct:
sqlite> explain query plan select distinct ppos from move join pos on mto = pnum where pcensus = 18 and pmin < pmax; selectid|order|from|detail 0|0|1|SCAN TABLE pos USING INDEX pipos 0|1|0|SEARCH TABLE move USING COVERING INDEX mrev (mto=?) Without distict: sqlite> explain query plan select ppos from move join pos on mto = pnum where pcensus = 18 and pmin < pmax; selectid|order|from|detail 0|0|1|SCAN TABLE pos 0|1|0|SEARCH TABLE move USING COVERING INDEX mrev (mto=?) With distinct: sqlite> explain select distinct ppos from move join pos on mto = pnum where pcensus = 18 and pmin < pmax; addr opcode p1 p2 p3 p4 p5 comment ---- ------------- ---- ---- ---- ------------- -- ------------- 0 Init 0 22 0 00 Start at 22 1 Null 1 7 0 08 r[7]=NULL 2 OpenRead 1 4 0 7 00 root=4 iDb=0; pos 3 OpenRead 3 6 0 k(1,) 00 root=6 iDb=0; pipos 4 OpenRead 4 11 0 k(3,,,) 02 root=11 iDb=0; mrev 5 Rewind 3 21 1 0 00 6 Seek 3 0 1 00 Move 1 to 3.rowid 7 Column 1 2 1 00 r[1]=pos.pcensus 8 Ne 2 20 1 (BINARY) 54 if r[1]!=r[2] goto 20 9 Column 1 5 3 -99 00 r[3]=pos.pmin 10 Column 1 6 4 99 00 r[4]=pos.pmax 11 Ge 4 20 3 (BINARY) 53 if r[3]>=r[4] goto 20 12 IdxRowid 3 5 0 00 r[5]=rowid 13 SeekGE 4 20 5 1 00 key=r[5] 14 IdxGT 4 20 5 1 00 key=r[5] 15 Column 3 0 6 00 r[6]=pos.ppos 16 Eq 6 19 7 (BINARY) 80 if r[7]==r[6] goto 19 17 Copy 6 7 0 00 r[7]=r[6] 18 ResultRow 6 1 0 00 output=r[6] 19 Next 4 14 0 00 20 Next 3 6 0 01 21 Halt 0 0 0 00 22 Transaction 0 0 155 0 01 usesStmtJournal=0 23 Integer 18 2 0 00 r[2]=18 24 Goto 0 1 0 00 Without distinct: sqlite> explain select ppos from move join pos on mto = pnum where pcensus = 18 and pmin < pmax; addr opcode p1 p2 p3 p4 p5 comment ---- ------------- ---- ---- ---- ------------- -- ------------- 0 Init 0 17 0 00 Start at 17 1 OpenRead 1 4 0 7 00 root=4 iDb=0; pos 2 OpenRead 2 11 0 k(3,,,) 02 root=11 iDb=0; mrev 3 Rewind 1 16 0 00 4 Column 1 2 1 00 r[1]=pos.pcensus 5 Ne 2 15 1 (BINARY) 54 if r[1]!=r[2] goto 15 6 Column 1 5 3 -99 00 r[3]=pos.pmin 7 Column 1 6 4 99 00 r[4]=pos.pmax 8 Ge 4 15 3 (BINARY) 53 if r[3]>=r[4] goto 15 9 Rowid 1 5 0 00 r[5]=rowid 10 SeekGE 2 15 5 1 00 key=r[5] 11 IdxGT 2 15 5 1 00 key=r[5] 12 Column 1 1 6 00 r[6]=pos.ppos 13 ResultRow 6 1 0 00 output=r[6] 14 Next 2 11 0 00 15 Next 1 4 0 01 16 Halt 0 0 0 00 17 Transaction 0 0 155 0 01 usesStmtJournal=0 18 Integer 18 2 0 00 r[2]=18 19 Goto 0 1 0 00 Will run analyze and re-run those when I get time. -----Original Message----- From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On Behalf Of Dominique Devienne Sent: Friday, February 03, 2017 3:55 AM To: SQLite mailing list Subject: Re: [sqlite] "DISTINCT" makes a query take 37 times as long On Fri, Feb 3, 2017 at 12:27 AM, Kevin O'Gorman <kevinogorm...@gmail.com> wrote: > But the big thing is that I took a look at EXPLAIN QUERY PLAN using this > ... Maybe somebody can explain them to me, but it doesn't really matter whether > I ever understand them. Perhaps Mr. Hipp can make use of them. > EXPLAIN, and EXPLAIN QUERY PLAN have completely different output. EXPLAIN is basically the SQLite "assembler" code (or its "bytecode" if you prefer), while EXPLAIN QUERY PLAN gives you a much more human readable high-level overview of the plan. The low-level plans do look identical, but please also share the high-level plan, should take you only a minute. --DD _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
