SQLite Forum

Defragment a table ?

Is there a way to "defragment" a table, without doing a full vacuum ?

After investing a performance issue, it boiled down to a "fragmentation" of particular table in a rather large database (stored on an SSD).

My table "A" has the same number rows as another table "B", and table "A" has just two fields (an integer primary key and a integer value field), but its rows are modified heavily and regularly, with no particular pattern. Table "B" has 6 fields, it is a snapshot view of data in "A" joined with other tables, and is recreated as a whole periodically.

When running queries against "A", they are repeatably about 3-4 times slower than the same queries against table "B", even with similar indexes, and even for a "select count(*)".

When on the other hand I export both tables to a blank DB, then queries running against table "A" become slightly faster.

Historically table "B" was created to alleviate the performance issue on "A", but it is quite a bit costly to recreate, and means the data presented is not as fresh as it could be.

I am not entirely sure what is fragmented exactly, and why/how it affects performance (storage layer is an SSD), any hints welcome!