inside Habbie's mind

This article on the SQLite wiki details how to efficiently maintain a small window on a big dataset without sacrificing performance, in environments that cannot maintain actual cursors, or where doing so is prohibitively expensive. Rule 2 in the article applies to other databases too; the method detailed in the article applies to any database in which keeping cursors is either impossible or expensive.

The article seems obvious; but I had not actually thought of that approach myself, so far.

When you’re using a lot of SQL from a programming language (be it PHP or C++), put comments inside all your queries indicating which function executed them. That way, logging becomes much more useful, like this:

sqlite3_trace: SELECT /* createobject uniquecontext */
   content FROM objects WHERE id=4

(I did not come up with this trick - but it is useful enough to post here anyway.)