On Sat, 9 Oct 2010 12:20:26 +0200, Joerg Sonnenberger <[email protected]> wrote:
>Hi all, >I'm seeing high disk IO and associated processing overhead in the >following situation, which shouldn't be as expensive as it currently is. > >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; > >Query plan: [snipped] >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. I would at most expect that if it had been defined as UNIQUE (symbol, file) -- (1) instead of UNIQUE (file, symbol) -- (2) Semantically both forms represent the same constraint. For the optimizer there could be a difference though: The first form (1) has the required column first, so it might not have to descend to the bottom of the index B-Tree. the optimizer might decide to use the index in this case. The second form (2) would force a full index scan, which is not much better than a table scan. The optimizer might decide differently after running ANALYZE; on a fully populated database. -- ( Kees Nuyt ) c[_] _______________________________________________ sqlite-users mailing list [email protected] http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
