Are you setting the constraintUsage return parameters correctly? SQLite ist asking two questions:
1) What is the cost of a full table scan on table inventory (your answer is 4581; I personally would leave idxNum=0 for this case) 2) What is the least cost of doing a partial table scan with any combination of the four constraints (resulting from the joins of t1->t2 on inventory_id and T4/5/6 -> t2 on film_id) In the second case, you should not only set rows and cost, but also indicate which constraint is to be used e.b. by setting constraintUsage[0].argvIndex = 1. This allows SQLite to pass the actual constraint value to the xFilter function. Also, if you can guarantee that your xFilter/xNext implementation will only return matching rows, you should set contraintUsage[0].omit = 1. This allows SQLite to skip the value check that would be coded to eliminate unwanted rows. Personally, I would be using small positive numbers for the indexes. Again, you should not be creating Indexes in xBestIndex; if you are actually creating indexes on request in xFilter, the cost of doing that should be included in the cost return value. Looking at your query, you will be returning the cartesian product of all actors and categories. I also find it strange to join inventory records to actors and categories instead of joining via the film table, as would be suggested by the n:m relational table names too. -----Urspr?ngliche Nachricht----- Von: Eric Hill [mailto:Eric.Hill at jmp.com] Gesendet: Freitag, 15. Mai 2015 18:03 An: sqlite-users at mailinglists.sqlite.org Betreff: [sqlite] Virtual Table query - why isn't SQLite using my indexes? 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 _______________________________________________ sqlite-users mailing list sqlite-users at mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users ___________________________________________ Gunter Hick Software Engineer Scientific Games International GmbH FN 157284 a, HG Wien Klitschgasse 2-4, A-1130 Vienna, Austria Tel: +43 1 80100 0 E-Mail: hick at scigames.at This communication (including any attachments) is intended for the use of the intended recipient(s) only and may contain information that is confidential, privileged or legally protected. Any unauthorized use or dissemination of this communication is strictly prohibited. If you have received this communication in error, please immediately notify the sender by return e-mail message and delete all copies of the original communication. Thank you for your cooperation.
