Hey,
Let me say up front that I'm sure this is my fault. I have SQLite version
3.8.9.
I am using virtual tables, and I am trying to get xBestIndex and xFilter doing
the right things so that I get optimal queries. Currently, I am working on the
following query:
SELECT t1.rental_date, t1.inventory_id, t1.customer_id,
t2.film_id,
t2.store_id, t3.first_name AS cust_firstname,
t3.last_name AS cust_lastname, t3.email,
t6.category_id, t4.title, t4.release_year,
t4.length,
t4.rating, t4.rental_rate, t5.actor_id, t8.name
AS category,
t7.first_name AS actor_firstname, t7.last_name
AS actor_lastname
FROM rental10 t1
LEFT OUTER JOIN inventory t2
ON ( t2.inventory_id =
t1.inventory_id )
LEFT OUTER JOIN customer t3
ON ( t3.customer_id =
t1.customer_id )
LEFT OUTER JOIN film_category t6
ON ( t6.film_id = t2.film_id )
LEFT OUTER JOIN film t4
ON ( t4.film_id = t2.film_id )
LEFT OUTER JOIN film_actor t5
ON ( t5.film_id = t2.film_id )
LEFT OUTER JOIN category t8
ON ( t8.category_id =
t6.category_id )
LEFT OUTER JOIN actor t7
ON ( t7.actor_id = t5.actor_id
);
When I execute this query, the result is correct, but it is taking too long by
an order of magnitude or two. It seems to be doing full table scans despite
the fact that I am creating indexes as requested.
For example, xBestIndex gets called for the inventory table twice, with four
constraints, once with usable set to false for all four constraints, and once
with usable set to true for all four. Here is my printf debugging spew:
jmpvtab BEST INDEX: Table: inventory nConstraints: 4
CONST[0]: 0 (inventory_id) = Unusable
CONST[1]: 1 (film_id) = Unusable
CONST[2]: 1 (film_id) = Unusable
CONST[3]: 1 (film_id) = Unusable
Index NOT created: est. cost: 4581
jmpvtab BEST INDEX: Table: inventory nConstraints: 4
CONST[0]: 0 (inventory_id) = Usable
CONST[1]: 1 (film_id) = Usable
CONST[2]: 1 (film_id) = Usable
CONST[3]: 1 (film_id) = Usable
Index created: est. cost: 3.66096029177608
So, in the first case, I do not create an index (which I signify by setting
idxNum to -999), and I set the cost (and, est. rows) to 4581. In the second
case, I create an index (0) and set cost to log10(4581) = 3.66 and est. rows to
4. Yet, later, whenever xFilter is called for the inventory table, SQLite
passes in idxNum = 999 and nConstraints = 0. The index I dutifully created is
never asked for. In cases where there is a single constraint, SQLite does ask
request the index in the xFilter call, but it seems that for all the cases
where multiple constraints are involved, the index is not being used.
I did EXPLAIN QUERY PLAN for the query and got this, consistent with what I'm
seeing:
0 0 0 SCAN TABLE rental10 AS t1 VIRTUAL
TABLE INDEX -999:
0 1 1 SCAN TABLE inventory AS t2 VIRTUAL
TABLE INDEX -999:
0 2 2 SCAN TABLE customer AS t3 VIRTUAL
TABLE INDEX 0:
0 3 3 SCAN TABLE film_category AS t6
VIRTUAL TABLE INDEX -999:
0 4 4 SCAN TABLE film AS t4 VIRTUAL
TABLE INDEX 0:
0 5 5 SCAN TABLE film_actor AS t5
VIRTUAL TABLE INDEX -999:
0 6 6 SCAN TABLE category AS t8 VIRTUAL
TABLE INDEX -999:
0 7 7 SCAN TABLE actor AS t7 VIRTUAL
TABLE INDEX 0:
Now, I know that SQLite is capable of efficiently performing this query,
because I also have the ability to copy these tables into SQLite so that I am
querying real tables instead of virtual tables. SQLite can perform the query
in under 1 second with real tables, but with virtual tables, it is taking > 25
seconds.
Any thoughts on what I can do to convince SQLite to use my indexes?
Thanks,
Eric
