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
