On 06/11/2015 03:49 AM, Eric Hill wrote: > Is it a bug that SQLite changes the order of the constraints passed to > xBestIndex based merely on the order of the ON clause, when SQL (AFAIK) says > nothing about what the order of the ON clause should be?
No. The order is undefined. However, it is less than perfect that the constraints on "film_id=" are marked usable when they are really not. That's the root of your problem I think - SQLite is asking for the wrong thing. When it eventually figures out that it can't actually use the plan it requested from xBestIndex (because the film_id= constraint is not actually usable) it falls back to a linear scan. There is now a change on the trunk that should fix this: http://www.sqlite.org/src/info/7b446771cadedafb Dan.
