Hello,
I have come across several situations in which the sqlite query optimizer
chooses clearly suboptimal query plans when a query spans several attached
databases. If I copy all involved tables into one sqlite database file, a
better query plan is chosen. The difference can be quite dramatic (orders of
magnitude faster).
Unfortunately it does not seem to happen in very simple databases with small
datasets, so I do not have a simple self-contained example I could upload, but
one general situation that goes wrong is
database a:
table a (id primary key, otherCOLS) table va(aid references a(id), foo)
database b:
table b (id primary key, otherCOLS) table vb(bid references b(id), foo)
database c:
table ab (aid, bid)
--references tables a.a and b.b
--indexed on aid and bid contains a smallish subset of all lines from a and b
query:
select count(*)
FROM ab
JOIN a ON(a.id = ab.aid)
JOIN b ON(b.id = ab.bid)
JOIN va ON(va.aid = a.id)
JOIN vb ON(vb.bid = b.id)
WHERE va.foo = 'rare value' AND vb.foo = 'rare value';
The optimizer chooses to create an automatic index on vb and then does the
query in order va, vb, a, b, ab However reordering to e.g. ab, a, va, b, vb is
much faster (if I either enforce it by hand or import all tables into one db).
Are there known issues with query optimization across different databases or
maybe performance tips? I ran analyze on all DBs. I use sqlite version 3.7.5 on
Linux (x86_64).
Thanks for this great software and any tips you might have about this issue.
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
