Indexes built before the bulk copy in a rebuild

Note — this works, but it is a documented trap or a cost the author may not have meant.

What it catches

In a table rebuild the copy is the expensive step. Creating the new table's indexes before INSERT ... SELECT makes SQLite maintain every index row by row during the copy — random B-tree inserts for each — instead of one sorted build per index afterwards. The manual's own recipe creates the indexes after the copy.

Fires on

CREATE TABLE orders_new (id INTEGER PRIMARY KEY, user_id INTEGER, qty INTEGER) STRICT;
CREATE INDEX orders_new_user ON orders_new (user_id);
INSERT INTO orders_new SELECT id, user_id, qty FROM orders;

Do this instead

CREATE TABLE orders_new (id INTEGER PRIMARY KEY, user_id INTEGER, qty INTEGER) STRICT;
INSERT INTO orders_new SELECT id, user_id, qty FROM orders;
CREATE INDEX orders_new_user ON orders_new (user_id);