5 ms·
I was not aware that SQLite creates temporary indexes in places where other databases would perform merge joins. https://sqlite.org/tempfiles.html#transient_in
by chasil 4y ago
I was not aware that SQLite creates temporary indexes in places where other databases would perform merge joins.
https://sqlite.org/tempfiles.html#transient_indices https://sqlite.org/tempfiles.html#transient_indices
SQLite also creates background indexes for ROWID, of which I was aware.
https://sqlite.org/rowidtable.html https://sqlite.org/rowidtable.html
- Scaevolus 4y agorowid isn't really a background index, but rather the normal storage layout, with non-integer primary keys having a separate index structure.
- yread 4y agowould be cool to get a diagnostic message when that happens
- chasil 4y agoFor transient indexes, or permanent ones? The transient indexes will be created in a separate, temporary file. They will be erased on a commit, rollback, or crash recovery. They will not persist.
- masklinn 4y agoPer the linked mailing list post there’s no dust but there is a back channel through which you can be notified: > SQLite comes with instrumentation (specifically the SQLITE_STMTSTATUS_AUTOINDEX verb for sqlite3_stmt_status() - http://www.sqlite.org/c3ref/stmt_status.html http://www.sqlite.org/c3ref/stmt_status.html) that can be used to detect when automatic indices are used and alert the developer through a back channel to this fact so that she can fix the problem with an appropriate CREATE INDEX. In other words, SQLite provides you with the tools to help you detect and eliminate the use of automatic indices.
- SigmundA 4y agoSQLite uses a b-tree storage format for tables, so all tables are an index, similar to clustered indexs in MSSQL Server and Index Organized Tables in Oracle. The RowID is the true primary key unless you specify the table should use the actual primary key as the row identifier, this is legacy quirk of Sqlite. Either way SQLite doesn't have heap tables like PG (which only has heap tables), all tables are clustered indexes.
- winrid 4y agoThis is not true in my experience at all. You have to create indexes. Also, a btree is created for rowid, according to these docs. https://www.sqlite.org/withoutrowid.html#:~:text=In%20an%20ordinary%20SQLite%20table,is%20a%20true%20PRIMARY%20KEY https://www.sqlite.org/withoutrowid.html#:~:text=In%20an%20o....
- SigmundA 4y agoWhat's not true? All tables in SQLite are b-tree indexes, the unique key is either the rowid or the primary key in "WITHOUT ROWID" tables. SQLite does not have unordered heaps: https://www.sqlite.org/lang_createtable.html#rowid https://www.sqlite.org/lang_createtable.html#rowid
- winrid 4y agoI 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.
- masklinn 4y ago> 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).
- 4y ago