[sqlite] Virtual tables/xBestIndex: Is this a bug?
Thanks, Dan. I grabbed the fix and tried it out - works great, and a much better fix than what I suggested (not shocking). It really cleans up xBestIndex. In my simple cases, it's like the usable flag is now superfluous; xBestIndex is only getting passed usable constraints (and not getting called it all when there are no usable constraints). But perhaps there are still scenarios where an unusable constraint will show up. Eric -Original Message- From: sqlite-users-bounces at mailinglists.sqlite.org [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On Behalf Of Dan Kennedy Sent: Thursday, June 11, 2015 1:14 AM To: sqlite-users at mailinglists.sqlite.org Subject: Re: [sqlite] Virtual tables/xBestIndex: Is this a bug? 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. ___ sqlite-users mailing list sqlite-users at mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Virtual tables/xBestIndex: Is this a bug?
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.
[sqlite] Virtual tables/xBestIndex: Is this a bug?
Hey, This is a follow-up from the thread entitled "Virtual Table query - why isn't SQLite using my indexes?" in order to raise the visibility of this issue: Consider this SQL, where all of the tables involved are virtual: SELECT t2.rental_id, t2.rental_date, t1.film_id, t3.title, t4.category_id, t5."name" FROM rental t2 LEFT OUTER JOIN inventory t1 ON ( t1.inventory_id = t2.inventory_id ) LEFT OUTER JOIN film t3 ON ( t3.film_id = t1.film_id ) LEFT OUTER JOIN film_category t4 ON ( t4.film_id = t1.film_id ) LEFT OUTER JOIN category t5 ON ( t5.category_id = t4.category_id ) ; When xBestIndex gets called for the inventory table, the constraints will be in this order: jmpvtab BEST INDEX: Table: inventory nConstraints: 3 CONST[0]: 0 (inventory_id) = Usable CONST[1]: 1 (film_id) = Usable CONST[2]: 1 (film_id) = Usable This is helpful, because the constraint that matters, i.e., the column that SQLite would benefit from having an index on, is listed first. However, if I instead submit this SQL: SELECT t2.rental_id, t2.rental_date, t1.film_id, t3.title, t4.category_id, t5."name" FROM rental t2 LEFT OUTER JOIN inventory t1 ON ( t2.inventory_id = t1.inventory_id ) LEFT OUTER JOIN film t3 ON ( t3.film_id = t1.film_id ) LEFT OUTER JOIN film_category t4 ON ( t4.film_id = t1.film_id ) LEFT OUTER JOIN category t5 ON ( t5.category_id = t4.category_id ) ; where the only difference is the order of the ON clause for the LEFT OUTER JOIN with inventory, the xBestIndex call looks like this: jmpvtab BEST INDEX: Table: inventory nConstraints: 3 CONST[0]: 1 (film_id) = Usable CONST[1]: 1 (film_id) = Usable CONST[2]: 0 (inventory_id) = Usable So, with just that tiny change, now the interesting constraint comes last instead of first. 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? I am attempting to create whatever indexes SQLite tells me it needs, but SQLite is playing a shell game with this information. Thanks very much! Eric
