Your xBestIndex function should be returning a cost that is proportional to the "effort required to fulfill the query". My own VT implementations have been returning the total number of records for queries with no constraints and assuming a constant fan-out factor for each key field provided as a constraint (eg. 25 for a full table scan, 5 if only the first of two key fields is provided and 1 if both are provided).
Newer releases of SQLite handle IN expressions differently. Older releases simply created an indexed ephemeral table and an Affinity opcode. Newer releases call xBestIndex twice; once with each IN transformed into an equality constraint, and once with (all of) these constraints disabled. Depending on the cost, the non-indexed ephemeral table would be placed on either side of the JOIN operation. I found it was necessary to increase the cost by at least 2.5% to discourage SQLite from putting the ephemeral table on the LHS (outer loop) of the join. -----Ursprüngliche Nachricht----- Von: sqlite-users [mailto:[email protected]] Im Auftrag von Max Vlasov Gesendet: Mittwoch, 17. April 2019 10:44 An: SQLite mailing list <[email protected]> Betreff: [EXTERNAL] [sqlite] "Optional" incoming parameters for virtual tables Hi, I'm trying to implement "optional incoming" parameters for my virtual tables. It's when there are columns required, but also there are some fine-tuning columns that should be noticed when provided in the query and assumed some default if not. The system that I tried seems to work, but I would like an opinion whether it is future-proof. - When the required parameters are not provided, xBestIndex returns a large cost for earlier sqlite version and additionallly SQLITE_CONSTRAINT for 3.26.0 onward. - When the required parameters provided and all optional provided the cost is 1. - For every optional parameter not provided, the cost is increased by 1. So if there are 3 optional parameters and no provided, the cost is 4, if one optional provided - the cost is 2. Should this always work as expected or sqlite might not always choose the lowest cost index amongst the ones with small values? I tested it with a couple of vt implementations, but the number of columns in both required/optional pool are low and I expect some trouble when the number will go up. Thanks, Max _______________________________________________ sqlite-users mailing list [email protected] http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users ___________________________________________ Gunter Hick | Software Engineer | Scientific Games International GmbH | Klitschgasse 2-4, A-1130 Vienna | FN 157284 a, HG Wien, DVR: 0430013 | (O) +43 1 80100 - 0 May be privileged. May be confidential. Please delete if not the addressee. _______________________________________________ sqlite-users mailing list [email protected] http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
