Choose Your Index
Why don’t we see more about clustered SQLite indexes? To rowid, or not to rowid, that is the question.
Looking at schemas I encounter in the wild, mostly created after 2013, this quote from the documentation strikes me as remarkable:
In an elegant system, all tables would behave as
WITHOUT ROWIDtables even without the
WITHOUT ROWIDkeyword. However, when SQLite was first designed, it used only integer rowids for row keys to simplify the implementation. This approach worked well for many years. But as the demands on SQLite grew, the need for tables in which the
PRIMARY KEYreally did correspond to the underlying row key grew more acute. The
WITHOUT ROWIDconcept was added in order to meet that need without breaking backwards compatibility with the billions of SQLite databases already in use at the time (circa 2013).
So, ignore the gospel—Stack Overflow et al.—choose a modern index.