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