SQLite uses a cost-based algorithm to formulate a query plan. Therefore you must return a proper cost estimate in your virtual tables' xBestIndex function. This is usually interpreted as similar to "how many (disk) accesses are required" or "how many records will be returned". For your virtual table these numbers would be 0 (but you should probably return 1) and 100 respectively.
The available query plans are a) FTS real table, FTS virtual table b) FTS virtual table, FTS real table c) Build index on real table, FTS virtual table, access covering index If you return 0, your estimates will be a) 1000000 * 0 = 0 b) 0 * 100000 = 0 c) 1000000 * log(1000000) + 0 * 10 = 6000000 So SQLite will choose either (a) or (b). If you return 1, your estimates will be a) 1000000 * 1 = 1000000 b) 1 * 1000000 = 1000000 c) 1000000 * log(1000000) + 1 * 10 = 6000010 If you return 100, your estimates will be a) 1000000 * 100 = 100000000 b) 100 * 1000000 = 100000000 c) 1000000 * log(1000000) + 100 * 10 = 6001000 So to make this work you need to return the number of rows... OTOH you can always force SQLite to use the query plan suggested by the order of joins using CROSS JOIN Gunter -----Ursprüngliche Nachricht----- Von: Eleytherios Stamatogiannakis [mailto:est...@gmail.com] Gesendet: Donnerstag, 14. März 2013 15:38 An: sqlite-users@sqlite.org Betreff: [sqlite] Query optimizer suboptimal planning with virtual tables Hi, I have came across a glitch with how SQLite's query optimizer plans virtual tables. Example follows: I have a virtual table function named "range" that produces all numbers from 1 to range's arg. This virtual table does not have any index functionality. With this i'll create the virtual table instance "t1": > create virtual table t1 using range('100'); > select * from t1; 1 2 ... 99 100 --Column names-- C1 Let's create a real table now: > create table t2 as select * from t1; The plan that the optimizer will produce when i join these two tables is this: > explain query plan select * from t1, t2 where t1.c1=t2.c1; 0 |0 |1 | SCAN > TABLE t2 (~1000000 rows) 0 |1 |0 | SCAN TABLE t1 VIRTUAL TABLE INDEX 0: (~0 > rows) Even thought, i have put VT t1 first in the join list, SQLite will do a nested loop join (putting it on the right). Wouldn't it had made more sense for SQLite to create an automatic index on the real table t2 and do the join as such? 0 |0 |0 | SCAN TABLE t1 VIRTUAL TABLE INDEX 0: (~0 rows) 0 |1 |1 | SEARCH TABLE t2 USING AUTOMATIC COVERING INDEX idx (C1=?) (~10 rows) Putting the VT on the right by default, doesn't make much sense to me, since it cannot create an automatic index on it. In general it seems to me to be a better default to always have the non automatic indexable SQLite entities (views, virtual tables) on the left of the joins and what can be automatically indexed on the right of the joins. Also, i think, that it would be even better if SQLite had the ability to scan the virtual table and build a temporary automatic covering index on it to do the join (why isn't this case allowed?). Thank you, lefteris. _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users -------------------------------------------------------------------------- Gunter Hick Software Engineer Scientific Games International GmbH Klitschgasse 2 – 4, A - 1130 Vienna, Austria FN 157284 a, HG Wien Tel: +43 1 80100 0 E-Mail: h...@scigames.at This e-mail is confidential and may well also be legally privileged. If you have received it in error, you are on notice as to its status and accordingly please notify us immediately by reply e-mail and then delete this message from your system. Please do not copy it or use it for any purposes, or disclose its contents to any person as to do so could be a breach of confidence. Thank you for your cooperation. _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
