The reason "select count(*) from t" is super fast is the special "count" opcode that does the "running" in just one go, instead of calling "Column", "AggStep" and "Next" in a loop.
-----Ursprüngliche Nachricht----- Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im Auftrag von Rowan Worth Gesendet: Montag, 18. September 2017 11:08 An: SQLite mailing list <sqlite-users@mailinglists.sqlite.org> Betreff: Re: [sqlite] [EXTERNAL] Number of rows in answer set On 18 September 2017 at 16:52, David Wellman <dwell...@ward-analytics.com> wrote: > To run a select statement the application code has to: > sqlite3_prepare: parse the sql, make sure it's valid, build the plan > (using "nifty heuristics" :-) ) sqlite3_step - this one runs the sql, > builds an answer set and then returns the first row > >>> AT THIS POINT the application doesn't know how many rows are in > >>> the > answer set only that there is at least 1. <<< (but presumably sqlite > does know how many there are) > This presumption does not hold, in general. SQLite has no magical background processing powers - each time you call sqlite3_step it picks up where it left off and does the work to retrieve the next result row. A similar common misconception (which I was guilty of) is that SQLite must know how many rows are in a particular table, so it can optimise "SELECT count(*) FROM t" to be super fast. It doesn't -- it needs to walk the btree to count the number of rows. -Rowan _______________________________________________ sqlite-users mailing list sqlite-users@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: 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@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
