SQLite WITHOUT ROWID Optimization

10 Dec 2014

What is WITHOUT ROWID?

By default, every row in SQLite has a special column called rowid. If the phrase WITHOUT ROWID is added to the end of a CREATE TABLE statement, then the special rowid column is dropped. That might give some space and performance advantages of omitting that column.

What is its use?

One advantage obviously is to save some space for tables created with WITHOUT ROWID phrase.

Another important optimization gained is search performance will improve especially for tables that have non-integer or composite (multi-column) PRIMARY KEYS and that do not store large strings or BLOBs.

Before understanding why, we need to know SQLite internal implementations of rowid tables and WITHOUT ROWID tables: traditional rowid tables are implmented as B*-Trees whereas WITHOUT ROWID tables are using ordinary B-Trees. B*-Trees store all content in the leaves while B-Tree store content on both leaves and intermediate nodes. That means WITHOUT ROWID tables might take up more disk space than its rowid equivalent and thus reduces the fan-out, increasing the search cost (search depth increased).

Also to keep in mind is, PRIMARY KEY in SQLite, including INT PRIMARY KEY, is really just a UNIQUE index in an ordinary rowid table. However, INTEGER PRIMARY KEY column in rowid table is an alias for the rowid and therefore is a true primary key. This indicates INTEGER PRIMARY KEY rowid table saves more space in intermediate nodes (compared to WITHOUT ROWID equivalent) AND has primary key also as the key to locate rows instead of needing another index tree to locate them.

In other words, WITHOUT ROWID optimization will only work under certain circumstances:

  1. For a single INTEGER PRIMARY KEY table, WITHOUT ROWID phrase will not out-perform the ordinary rowid tables: do NOT use WITHOUT ROWID on INTEGER PRIMARY KEY tables.
  2. For non-integer or composite primary key tables, if the primary key column(s) store large strings or BLOBs, this technique is more likely to loose its advantages due to the underlying B-Tree implementation. In other words, use it on non-integer or composite primary SMALL-SIZED key(s) tables.

REFERENCE