Hello, I've looked for an explaination in the following pages without success: http://www.sqlite.org/optoverview.html http://www.sqlite.org/cvstrac/wiki?p=QueryPlans
So I hope someone can help me... CREATE TABLE node( id INTEGER PRIMARY KEY, name TEXT ); CREATE TABLE edge( orig INTEGER REFERENCES node, dest INTEGER REFERENCES node, PRIMARY KEY(orig, dest) -- A unique index on (orig, dest) is created here automatically ); INSERT INTO node VALUES (1, 'node1'); INSERT INTO node VALUES (2, 'node2'); INSERT INTO node VALUES (3, 'node3'); INSERT INTO node VALUES (4, 'node4'); INSERT INTO edge VALUES (1, 2); INSERT INTO edge VALUES (1, 3); INSERT INTO edge VALUES (1, 4); INSERT INTO edge VALUES (2, 3); INSERT INTO edge VALUES (2, 4); ANALYZE; -- This Query uses the index as expected. EXPLAIN QUERY PLAN SELECT n2.name FROM node n1 INNER JOIN edge e on e.orig = n1.id INNER JOIN node n2 on n2.id = e.dest WHERE n1.id = 2; --order from detail ------------ ---------- ---------------------------------- --0 0 TABLE node AS n1 USING PRIMARY KEY --1 1 TABLE edge AS e WITH INDEX sqlite_ --2 2 TABLE node AS n2 USING PRIMARY KEY -- This query don't EXPLAIN QUERY PLAN SELECT n2.name FROM node n1 INNER JOIN edge e on e.dest = n1.id INNER JOIN node n2 on n2.id = e.orig WHERE n1.id = 2; --order from detail ------------ ---------- ---------------------------------- --0 0 TABLE node AS n1 USING PRIMARY KEY --1 1 TABLE edge AS e --2 2 TABLE node AS n2 USING PRIMARY KEY -- But if an index is created with the columns inverted CREATE INDEX edge_idx ON edge(dest,orig); ANALYZE; -- The same query uses it. EXPLAIN QUERY PLAN SELECT n2.name FROM node n1 INNER JOIN edge e on e.dest = n1.id INNER JOIN node n2 on n2.id = e.orig WHERE n1.id = 2; --order from detail ------------ ---------- ---------------------------------- --0 0 TABLE node AS n1 USING PRIMARY KEY --1 1 TABLE edge AS e WITH INDEX edge_id --2 2 TABLE node AS n2 USING PRIMARY KEY Regards. _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
