SQLite db optimization with ROWID

Posted on 04 Jul, 2022
SQLite lets you improve your database performance by using a phrase "WITHOUT ROWID" in create table command
A WITHOUT ROWID table is a table that uses a Clustered Index as the primary key.
CREATE TABLE IF NOT EXISTS plays(
  word TEXT PRIMARY KEY,
  chapter INTEGER
) WITHOUT ROWID;
When to use
when your schema has composite multi-column primary keys
when your primary key is not likely an INTEGER
Resources
​Clustered Indexes and the WITHOUT ROWID Optimization - Must Read

Last modified 10mo ago