"fred" is only known if your select statement references only that one table and the value is supplied as a literal.
If the value is a bound variable or part of an (explicit or implicit) join expression the value(s) (there may be more than one) will not be known until well after the sqlite3_prepare() call. -----Ursprüngliche Nachricht----- Von: Dominique Devienne [mailto:ddevie...@gmail.com] Gesendet: Freitag, 04. Juli 2014 16:22 An: General Discussion of SQLite Database Betreff: Re: [sqlite] Error xBestIndex returned an invalid plan On Fri, Jul 4, 2014 at 2:50 PM, Hick Gunter <h...@scigames.at> wrote: > "SELECT * FROM person WHERE name = 'fred';" will have SQLite asking "what is > the cost of a partial table scan on field name" ( {name, '='} ) and the > answer should be the average number of entries that must be read to locate > all matching entries ( e.g. the cost of finding the first one plus the > average number of duplicates: ld n + (n / distinct names) ). You need to > record that the first argv is the name. What bugs me is that you don't get to know about 'fred', i.e. you must return a cost without knowing what value the query will use (bind peeking [1]), which in the case of range queries (non-unique index on the column used by the where clause) can make a big difference. Many of my virtual tables are based on C++ associative containers which implement equal_range() efficiently, such that I could use it to obtain an exact cardinality with zero I/O involved, but SQLite doesn't allow me to do that. And I think that's a shame. You don't even have access to it when it's a literal value, not only bind values. Of course, when the value comes from a subquery, or via a join, that's different, but in the case of literals and binds, that's a shame. My $0.02. --DD [1] https://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:492078000346228806 _______________________________________________ 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
