I went through the following article on query planning in SQLite. However, I do not understand the following, stated in Sec. 1.2:
One technique for avoiding a full table scan is to do lookups by rowid (or by the equivalent INTEGER PRIMARY KEY). To lookup the price of peaches, one would query for the entry with a rowid of 4:
SELECT price FROM fruitsforsale WHERE rowid=4;Since the information is stored in the table in rowid order, SQLite can find the correct row using a binary search.
Is that really true - is the information in the table sorted by the rowid (primary key) by default?
Yes, this is true, see https://www.sqlite.org/lang_createtable.html#rowid:
The data for rowid tables is stored as a B-Tree structure containing one entry for each table row, using the rowid value as the key. This means that retrieving or sorting records by rowid is fast. Searching for a record with a specific rowid, or for all records with rowids within a specified range is around twice as fast as a similar search made by specifying any other PRIMARY KEY or indexed value.