Keith Medcalf wrote > but cannot be re-ordered for OUTER JOINS. Actually, I think order requirements (or rather, row grouping requirements, as far as I can paint it the requirement is just that all outer joined rows come in a bunch for each main join tuple) would not be violated if the join is made on an unique key left-side and an index is used right-side :) or something similar. I don't know, extensive algebra must be involved :) Even without index order inference, the main trunk keys can be sorted in a temp structure to preserve the condition, like in a GROUP BY query. However, I do see that SQLite seems to actually do it (scan b before a I mean) if I reqrite the query as I showed.
select * from a, b where a.a *= b.a was replaced by select * from a LEFT JOIN b ON a.a = b.a </> Right, right, and with this in mind you can see my problem with the query is so easy to understand: My query, on the old format, is: select * from a, b where a.a *= b.a AND b.c = 5 My "improved" query, on the old format: select * from a, b where a.a *= b.a AND b.c *= 5 AND b.c = 5 // (OR IS NOT NULL) You can see the b.c *= 5 (JOIN ON ... AND b.c=5 ... WHERE b.c IS NOT NULL) is redundant, because it's just a weaker predicate, and I needed to add it just as an index hint on the join loop to trigger the right execution plan. Keith Medcalf wrote > I would sincerely doubt that there is *any* SQL optimizer or query planner > that can optimize mis-spoken queries containing OUTER JOINS. I don't know about mis-spoken, I don't think anything is mis-spoken. This app is running fine on Maria, I'm in the process of porting in to SQLite. I wouldn't have picked on this query unless it was lagging behind orders of magnitude (2.5s vs 50ms). So I think Maria does it (I haven't bothered to check the execution plan there, went straight to hacking SQLite). -- Sent from: http://sqlite.1065341.n5.nabble.com/ _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
