My point was it's not super fast though -- rather than being an O(1) lookup it uses I/O and compute proportional to the number of rows in the table (which I first noticed when a SELECT count(*) was taking minutes on a 50GB database).
I'm not complaining, and I'm aware you can use triggers to implement that sort of functionality where desired. My point is just that one should be carefule regarding presumptions as sqlite tends to not do anything more than what it has been asked to do (which is a good thing!). -Rowan On 18 September 2017 at 17:15, Hick Gunter <h...@scigames.at> wrote: > 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 > _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
