Aha ok, great!

Now, forgive me, but there is still a difference in the byte code, and I'm
having a hard time decyphering it. The difference from the non-distinct and
the distinct is:

* The table t1 is opened
* There is an extra Seek in the scanning loop, it looks like it moves the
read pointer of table t1 to the indexed rowid. There are no reads
occurring, but won't the seek cause I/O?

See log below.

sqlite> create index tb on t1(b);
sqlite> select distinct b from t1;
--EQP-- 0,0,0,SCAN TABLE t1 USING COVERING INDEX tb
addr  opcode         p1    p2    p3    p4             p5  comment
----  -------------  ----  ----  ----  -------------  --  -------------
0     Init           0     11    0                    00  Start at 11
1     Null           1     2     0                    08  r[2]=NULL
2     OpenRead       2     4     0     k(2,,)         00  root=4 iDb=0; tb
3     Explain        0     0     0     SCAN TABLE t1 USING COVERING INDEX
tb  00
4     Rewind         2     10    1     0              00
5       Column         2     0     1                    00  r[1]=t1.b
6       Eq             1     9     2     (BINARY)       80  if r[2]==r[1]
goto 9
7       Copy           1     2     0                    00  r[2]=r[1]
8       ResultRow      1     1     0                    00  output=r[1]
9     Next           2     5     0                    01
10    Halt           0     0     0                    00
11    Transaction    0     0     3     0              01  usesStmtJournal=0
12    Goto           0     1     0                    00
Error: index tb already exists
sqlite> select distinct length(b) from t1;
--EQP-- 0,0,0,SCAN TABLE t1 USING INDEX t
addr  opcode         p1    p2    p3    p4             p5  comment
----  -------------  ----  ----  ----  -------------  --  -------------
0     Init           0     13    0                    00  Start at 13
1     Null           1     2     0                    08  r[2]=NULL
2     OpenRead       0     2     0     2              00  root=2 iDb=0; t1
3     OpenRead       2     3     0     k(2,,)         00  root=3 iDb=0; t
4     Explain        0     0     0     SCAN TABLE t1 USING INDEX t  00
5     Rewind         2     12    1     0              00
6       Seek           2     0     0                    00  Move 0 to
2.rowid
7       Column         2     0     1                    00  r[1]=
8       Eq             1     11    2                    80  if r[2]==r[1]
goto 11
9       Copy           1     2     0                    00  r[2]=r[1]
10      ResultRow      1     1     0                    00  output=r[1]
11    Next           2     6     0                    01
12    Halt           0     0     0                    00
13    Transaction    0     0     3     0              01  usesStmtJournal=0
14    Goto           0     1     0                    00



On Fri, Aug 11, 2017 at 1:31 PM Richard Hipp <d...@sqlite.org> wrote:

> On 8/11/17, Wout Mertens <wout.mert...@gmail.com> wrote:
> > So, am I correct in thinking that an index on expressions already has all
> > the required data to answer e.g. a SELECT DISTINCT?
> >
> > If so, that could be an optimization?
> >
> > Can I request this optimization to be made? :)
> >
>
> That optimization is already made.  Look at the byte-code:
>
> CREATE TABLE t1(a,b,c);
> CREATE INDEX t1x1 ON t1(length(b));
> .eqp full
> SELECT DISTINCT length(b) FROM t1;
>
> The problem is that when the "Explain" instruction (which gives the
> "explain query plan" output) is generated, the query planner does not
> yet realize that it can get by with only using the index.  It never
> actually uses the original table - it only uses the index - but at the
> time that "Explain" is generated, that fact is unknown.  And so the
> EXPLAIN QUERY PLAN output is not 100% accurate.
>
> I think fixing that should be low-priority.
>
> --
> D. Richard Hipp
> d...@sqlite.org
> _______________________________________________
> 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

Reply via email to