BTW: SQLite will also ask your virtual table about GROUP BY/ORDER BY capabilities if either of the clauses is included in the SELECT.
-----Ursprüngliche Nachricht----- Von: Hick Gunter [mailto:h...@scigames.at] Gesendet: Montag, 12. Jänner 2015 08:02 An: 'General Discussion of SQLite Database' Betreff: Re: [sqlite] xBestIndex/XFilter and virtual tables SQLite is asking your virtual table questions: 1) what is the cost of a full table scan? (your answer: 1000) 2) what is the cost of a lookup by "id"? (your answer: 1) 3) when performing a key lookup, do you promise to return only rows matching the key? (your answer in returned in the "omit" field) Quesition 1 establishes the base line cost of a full table scan. SQLite knows it can answer any query for a constant set of constraints by incurring this cost Question 2 establishes the cost of retrieving a single row. The answer 1 is probably somewhat overoptimistic in terms of real cost which is O(log n). SQLite now needs to determine if scanning once (1000) is more or less costly than performing n key lookups. This cost depends on the answer to Question 3. If you promise only to return rows matching the given id, the cost is (n * 1) => SQLite will choose to perform n calls to xFilter, once for each element of the key set. If you cannot promise to return only the matching rows, how many rows will you return? The conservative estimate is "no more than there are rows in the table" (=1000) => SQLite must choose to scan the table once. -----Ursprüngliche Nachricht----- Von: Venkat Murty [mailto:vmu...@fiberlink.com] Gesendet: Sonntag, 11. Jänner 2015 23:52 An: sqlite-users@sqlite.org Betreff: Re: [sqlite] xBestIndex/XFilter and virtual tables >Estimated cost and row for each call to xBestIndex (sqlite3_index_info is >filled with the below values for each call) > >a) When there is one non-usable constraint > idxNum = 0 estimatedCost = 1000.000000 estimatedRows = 1000 > >b) when there is one usable constraint > idxNum = 1 estimatedCost = 1.000000 estimatedRows = 1 > >xFilter is called with idxNum = 0 and argc = 0 If aConstraintUsage[0].omit = 1 then xFilter is called multiple times with idxNum = 1. If aConstraintUsage[0].omit = 0 then xFilter is called with idxNum = 0 and argc = 0 Fiberlink Disclaimer: The information transmitted is intended only for the person or entity to which it is addressed and may contain confidential and/or privileged material. Any review, retransmission, dissemination or other use of, or taking of any action in reliance upon, this information by persons or entities other than the intended recipient is prohibited. If you received this in error, please contact the sender and delete the material from any computer. _______________________________________________ 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 FN 157284 a, HG Wien Klitschgasse 2-4, A-1130 Vienna, Austria Tel: +43 1 80100 0 E-Mail: h...@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. _______________________________________________ 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 FN 157284 a, HG Wien Klitschgasse 2-4, A-1130 Vienna, Austria Tel: +43 1 80100 0 E-Mail: h...@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. _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
