Hi all,
I've trouble optimizing for an N:M mapping table. The schema of the
table is this:
CREATE TABLE map (n INTEGER NOT NULL, m INTEGER NOT NULL);
I want to retrieve a list of n filtered on the presence of certain
values of m, e.g. give me all n for which there is an m = 3 and m = 5,
but no m = 7.
A naive query would look like this:
SELECT a.n FROM map a, map b, map c
WHERE a.n = b.n AND a.n = c.n AND a.m = 3 AND b.m = 5
AND c.id not in (select id from map where c.m = 7);
This can be slow, even for the more simple case with only positive selection:
SELECT a.n FROM map a, map b
WHERE a.n = b.n AND a.m = 3 AND b.m = 5;
And this variation does not make it a lot faster:
SELECT n FROM map WHERE m = 3 INTERSECT SELECT n FROM map where m = 5;
There are about a million entries in the table map and want to
increase to about 10 million.
The current indexes are
CREATE INDEX map_n ON map(n);
CREATE INDEX map_m ON map(n,m);
Is there a cleverer way of doing these queries?
The fraction of n's that has a particular m can be anywhere between 0 and 1.
Cheers,
Jos
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
