I vote to not control manual table join order with PRAGMAs, as PRAGMAs are too course-grained to be of any practical use in complicated queries. For example, what happens if you want to force a join on just a couple of tables in the FROM clause, but not others? It is not possible without an a set of very awkward query-specific PRAGMA commands. (For that matter, I'm not a fan of Oracle optimization hints in comments either, although it is a lesser evil.)
I think the CROSS JOIN syntax is an ideal way to manually control the join order for a number of reasons: * it's backwards compatible with older versions of Sqlite (i.e., it will not degrade performance prior to 3.2.3 - the queries would run exactly as they did before) * the very simple CROSS JOIN syntax - merely replace commas in the from clause with " CROSS JOIN " * CROSS JOIN is ANSI SQL compatible * CROSS JOIN is almost never used in practise, so its explicit use suggests the intention to manually force a join order. * Very elegant - just a few lines of code to implement it in Sqlite. * Postgres already uses this exact same CROSS JOIN heuristic to manually control the join order of tables, so there is a precedent: http://www.postgresql.org/docs/7.1/interactive/explicit-joins.html SELECT * FROM a CROSS JOIN b, c, d, e WHERE ...; forces the planner to join A to B before joining them to other tables... __________________________________________________ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com
