SQLite has what they "call a covering index", see point 9 here: https://www.sqlite.org/optoverview.html
My impression is that this mechanism is less general than what one finds in full-fat client-server SQLite databases.
My impression is that this mechanism is less general than what one finds in full-fat client-server SQLite databases.
I guess it's less of an issue in sqlite than in databases with richer datatypes in the sense that all datatypes are ordered and thus indexable.
CREATE INDEX tab_x_y
ON tab(x) INCLUDE (y);- it's not constrained (e.g. to be orderable)
- it does not affect the behaviour of the index, so you can have covering data in a UNIQUE index, or in a PK constraint (although for the latter one might argue a clustered index is superior)
- it only takes space in leaf nodes, not interior nodes, so you can have better occupancy of interior node pages, less pages to traverse during lookup, and they have better cache residency
- and finally the intent is clearer, when you put everything in the key it does not tell the reader what's what and why it there, and thus makes it harder to evaluate changes