Hi - >> I was under the impression that the order of fields in the ON clause >> doesn't matter. Is this impression incorrect? > It doesn't matter for the answer. You get the same result either way, > right? Just one way is faster than the other. True, I do get the same result.
> If interchanging the order of terms in an ON clause makes a performance > difference, that means that there are (at least) two possible ways of > evaluating the query. SQLite is looking at both possible plans and > estimating the run-time for each, and it is coming up with the same > estimate in both cases - a tie. The ambiguity is resolved in favor of the > plan it looked at first. And the order in which the plans are examined is > probably determined (to some extent) by the order of the terms in the ON > clause. I'd be surprised if the engine was evaluating the same time for each, given that the slower one is a good few hundred times slower - unless there's something about evaluation which I've fundamentally misunderstood. > If you run ANALYZE on your database, SQLite will have more information with > which to estimate the run-time of each plan, and is more likely to choose > the faster on. At the very least, it much less likely for the estimated > run-times to tie. Even better is if you compile with SQLITE_ENABLE_STAT3 > and then run ANALYZE. ANALYZE run; still the same result. Thanks, Hamish > > > -- > D. Richard Hipp > d...@sqlite.org > _______________________________________________ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users -- Hamish Symington ham...@lightbluesoftware.com Twitter: @lighterblue Winner of the 2009 FileMaker CubeAwards 'Best Private Sector Solution' award Light Blue Software is a company registered in England and Wales, number 6671025. Our registered office is 101 Teversham Drift, Cambridge CB1 3LL. _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
