Quoth Joerg Sonnenberger <[email protected]>, on 2010-10-09 12:20:26 +0200:
> Schema:
> CREATE TABLE symbol (file integer NOT NULL REFERENCES file (id), symbol
> varchar NOT NULL, revision varchar NOT NULL, branch boolean NOT NULL,
> UNIQUE (file, symbol));
>
> Query:
> SELECT DISTINCT symbol FROM symbol;
[...]
> What I expect to see is an index scan on the index of the UNIQUE constrain
> and picking the value without ever touch the table.
How would this happen? The index uses the columns in order, as far as
I know, so you'll have for instance ('file A', 'symbol 1'), then later
('file B', 'symbol 1') and so on, and you can't trivially get the
DISTINCT out of that without sorting in temporary storage.
Using UNIQUE (symbol, file) instead would seem the obvious solution.
Is there a reason you can't do that?
> Joerg
---> Drake Wilson
_______________________________________________
sqlite-users mailing list
[email protected]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
