In SQLite, when does declaring a table WITHOUT ROWID speed up lookups, and what do you give up by doing it?
answer
- one B-tree instead of two
- non-integer or composite key
- small rows, under a twentieth of a page
- no AUTOINCREMENT, no update hook
basics
~20 sWITHOUT ROWID stores a table as one B-tree keyed by its primary key, so a lookup on a non-integer or composite key takes one search instead of two. It suits small rows and costs AUTOINCREMENT, rowid functions and the update hook.
solid answer
~40 sAn ordinary SQLite table is a B-tree keyed by the hidden integer rowid, so `phone TEXT PRIMARY KEY` is really a separate unique index, `sqlite_autoindex_phone_lookup_1`, holding the phone and the rowid. A lookup searches that index, then the table: `SEARCH phone_lookup USING INDEX sqlite_autoindex_phone_lookup_1 (phone=?)`. Declared `WITHOUT ROWID`, the table is a single B-tree clustered on the primary key and the plan reads `SEARCH phone_lookup USING PRIMARY KEY (phone=?)`; the documentation cites about half the space and nearly twice the speed in favourable cases. It pays for non-integer or composite keys and rows under about a twentieth of a page. In exchange it needs a PRIMARY KEY, forbids AUTOINCREMENT, enforces NOT NULL on key columns, and gets no `last_insert_rowid()`, incremental BLOB I/O or `sqlite3_update_hook()` callbacks.
code
console · 6 linessqlite> CREATE TABLE phone_lookup(phone TEXT PRIMARY KEY, contact_id INTEGER NOT NULL) WITHOUT ROWID;
sqlite> EXPLAIN QUERY PLAN SELECT contact_id FROM phone_lookup WHERE phone = '+420601234567';
QUERY PLAN
`--SEARCH phone_lookup USING PRIMARY KEY (phone=?)
sqlite> PRAGMA index_info(phone_lookup);
0|0|phonego deeper
Remember that ordinary SQLite tables are keyed by a hidden rowid and that WITHOUT ROWID makes the primary key the storage key instead.
Explain the two-B-tree lookup it removes, which key and row shapes benefit, and the restrictions such as AUTOINCREMENT and NOT NULL on key columns.
Measure before converting, check row sizes against the page size, and confirm nothing in the app relies on rowid functions, BLOB streaming or the update hook.
Decide whether the schema standard allows WITHOUT ROWID at all, weighing modest speed and space gains against restrictions that tooling and change notification may trip over.
## How an ordinary SQLite table is stored Every ordinary SQLite table is a B-tree keyed by its **rowid**, a hidden 64-bit integer. A column declared `INTEGER PRIMARY KEY` becomes an alias for that rowid, so its primary key really is the storage key. How the rowid and `INTEGER PRIMARY KEY` work as types belongs to the material on SQLite's type affinity; here the point is the index consequence. Any other primary key, such as `TEXT PRIMARY KEY` or a two-column key, is implemented as a separate **unique index**, a second B-tree named like `sqlite_autoindex_<table>_1`: ```sql CREATE TABLE phone_lookup(phone TEXT PRIMARY KEY, contact_id INTEGER NOT NULL); -- SEARCH phone_lookup USING INDEX sqlite_autoindex_phone_lookup_1 (phone=?) ``` A caller-ID lookup by phone number therefore does two binary searches: one in the index to find the rowid, one in the table to fetch `contact_id`. The phone text is stored twice, once in each B-tree. ## What WITHOUT ROWID changes ```sql CREATE TABLE phone_lookup(phone TEXT PRIMARY KEY, contact_id INTEGER NOT NULL) WITHOUT ROWID; -- SEARCH phone_lookup USING PRIMARY KEY (phone=?) ``` - The table becomes a single B-tree whose key is the primary key, which is what other engines call a **clustered index**. - A primary-key lookup is one search, and the key is stored once. The documentation says this can use about half the disk space and run nearly twice as fast in favourable cases; with secondary indexes the gain is smaller. - Secondary indexes on the table end with the primary-key columns instead of a rowid, so an index on `contact_id` covers `SELECT phone ... WHERE contact_id = ?`. A long primary key also makes every secondary index longer. ## When it pays off | Table shape | Better choice | |---|---| | Single `INTEGER PRIMARY KEY` | Ordinary rowid table, which is faster here | | Text, UUID or composite primary key, small rows | `WITHOUT ROWID` | | Rows carrying large strings or BLOBs | Ordinary rowid table | Two shapes come up constantly in app schemas: - A **lookup table keyed by text**, such as the caller-ID table above or a settings table keyed by name. - A **link table with a composite key**, such as `contact_tags(contact_id, tag, PRIMARY KEY(contact_id, tag))`. As a rowid table it stores both columns twice, in the table and in the constraint's index; as `WITHOUT ROWID` it stores them once and every lookup by `contact_id` is a `SEARCH ... USING PRIMARY KEY (contact_id=?)`. The rule of thumb is that an average row should be smaller than about **one twentieth of a page**: about 200 bytes with the default 4096-byte page. Rowid tables keep all content in leaf pages, while a WITHOUT ROWID table stores content in interior B-tree pages too, so large rows reduce fan-out and deepen the tree. The documentation recommends deciding late: build with ordinary tables, then measure whether `WITHOUT ROWID` helps each table with a non-integer key and keep it only where it does. ## What you give up 1. A `PRIMARY KEY` is mandatory; without one, CREATE TABLE fails with `PRIMARY KEY missing on table ...`. 2. `INTEGER PRIMARY KEY` loses its rowid-alias meaning and behaves like any integer key, so an insert that omits it fails with a NOT NULL error instead of getting a new rowid. 3. `AUTOINCREMENT` is rejected with `AUTOINCREMENT not allowed on WITHOUT ROWID tables`. 4. `NOT NULL` is enforced on every primary-key column, as the SQL standard requires. Ordinary rowid tables, because of a long-kept legacy bug, accept NULL in a non-integer primary key. 5. `sqlite3_last_insert_rowid()` and the `last_insert_rowid()` function are not changed by inserts into the table. 6. Incremental BLOB I/O (`sqlite3_blob_open()`) cannot be used, because it addresses rows by rowid. 7. `sqlite3_update_hook()` does not fire for changes to the table, so any change-notification code built on that hook goes silent. 8. The file needs SQLite 3.8.2 (2013) or later to open. ## Recognising one `PRAGMA table_info` looks the same for both kinds of table. `PRAGMA index_info(phone_lookup)` is the reliable test: it returns the primary-key columns for a WITHOUT ROWID table and no rows for an ordinary one. In `sqlite_stat1`, a WITHOUT ROWID table's own B-tree appears as a row whose `idx` equals its `tbl`.
- In SQLite, why is WITHOUT ROWID a poor choice for a table keyed by `id INTEGER PRIMARY KEY`?In an ordinary table that column already is the rowid, the storage key, so lookups are single searches and there is no separate primary-key index to remove. The documentation says ordinary rowid tables run faster in that case and advises against WITHOUT ROWID tables with a single INTEGER PRIMARY KEY column.
- In SQLite, how does a WITHOUT ROWID table's primary key affect its secondary indexes?Each secondary-index entry ends with the primary-key columns where an ordinary table's would end with the rowid. That makes the primary-key columns covered for free, but a long text or composite key makes every secondary index larger, which reduces the savings when a table has several of them.
- WITHOUT ROWID adds features that ordinary rowid tables cannot provide.
- A WITHOUT ROWID table can use AUTOINCREMENT on an INTEGER PRIMARY KEY.
- WITHOUT ROWID is always faster, including for rows holding large BLOBs.
- Ordinary SQLite tables already reject NULL in any PRIMARY KEY column.
- sqlite3_update_hook() reports changes to WITHOUT ROWID tables as usual.