I see what you're saying. Just the wording you're using is confusing IMO. The tables themselves aren't btree indexes, there isn't any sort of "clustered" index on all columns. It's just that the tables rows are entries in a btree, indexed by rowid.
The tables themselves aren't btree indexes, there isn't any sort of "clustered" index on all columns.
What do you think a btree index is exactly? Especially a covering index?
It's just that the tables rows are entries in a btree, indexed by rowid.
That’s what a clustered index (/ table) is. It’s when the table's storage is organised by the chosen clustering key. In SQLite, all tables are clustered, and the clustering key is the ROWID, or the primary key for tables WITHOUT ROWID. You can think of it as the table being its own covering index (keyed on the clustering key, and INCLUDE-ing all other columns).
By opposition a database which uses heap tables (like postgres) needs a separate index for its PK (it also needs one to enforce unicity so that's two birds one stone).
I see. When I think of a covering index, I think of a covered query, where all query data can be fetched from the index itself. Doesn't the rowid btree just point to an offset on disk where the actual row is? Or is the row itself in the index?
The tables themselves aren't btree indexes, there isn't any sort of "clustered" index on all columns. It's just that the tables rows are entries in a btree, indexed by rowid.
That's what a clustered index is, the table is the index, the primary key is the key all the rest of the columns are in the value rather than the value being a row id that needs a second lookup to get to the data.
You can still have secondary indexes that point to the key of the clustered index.
SQLite is quirky in that even if you define a primary key, it has a hidden actual primary key called the rowid, unless you define it "WITHOUT ROWID" which was added later, this is what they say in the docs:
"WITHOUT ROWID is found only in SQLite and is not compatible with any other SQL database engine, as far as we know. In an elegant system, all tables would behave as WITHOUT ROWID tables even without the WITHOUT ROWID keyword. 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 KEY really did correspond to the underlying row key grew more acute. The WITHOUT ROWID concept 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)."
Comments
I see what you're saying. Just the wording you're using is confusing IMO. The tables themselves aren't btree indexes, there isn't any sort of "clustered" index on all columns. It's just that the tables rows are entries in a btree, indexed by rowid.
What do you think a btree index is exactly? Especially a covering index?
That’s what a clustered index (/ table) is. It’s when the table's storage is organised by the chosen clustering key. In SQLite, all tables are clustered, and the clustering key is the ROWID, or the primary key for tables WITHOUT ROWID. You can think of it as the table being its own covering index (keyed on the clustering key, and INCLUDE-ing all other columns).
By opposition a database which uses heap tables (like postgres) needs a separate index for its PK (it also needs one to enforce unicity so that's two birds one stone).
I see. When I think of a covering index, I think of a covered query, where all query data can be fetched from the index itself. Doesn't the rowid btree just point to an offset on disk where the actual row is? Or is the row itself in the index?
The value of the rowid btree is the row.
oh, nice! thx for the responses
That's what a clustered index is, the table is the index, the primary key is the key all the rest of the columns are in the value rather than the value being a row id that needs a second lookup to get to the data.
https://en.wikipedia.org/wiki/Database_index#Clustered
You can still have secondary indexes that point to the key of the clustered index.
SQLite is quirky in that even if you define a primary key, it has a hidden actual primary key called the rowid, unless you define it "WITHOUT ROWID" which was added later, this is what they say in the docs:
"WITHOUT ROWID is found only in SQLite and is not compatible with any other SQL database engine, as far as we know. In an elegant system, all tables would behave as WITHOUT ROWID tables even without the WITHOUT ROWID keyword. 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 KEY really did correspond to the underlying row key grew more acute. The WITHOUT ROWID concept 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)."
https://www.sqlite.org/withoutrowid.html#quirks
Yeah I know about rowid, I've used (exploited?) :) that a few times.