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));
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
sqlite-users mailing list