change sqlite file size after "DELETE FROM table"
SqliteSqlite Problem Overview
I am working with sqlite3 file.
First, I entered relatively big database, file size was about 100 mb.
Than I made
$db->exec("DELETE FROM table");
and entered just a small part of that database. But file size remained 100 mb.
What should you do to change sqlite file size when deleting it's content?
Sqlite Solutions
Solution 1 - Sqlite
The command you are looking for is vacuum. There is also a pragma to turn auto-vacuuming on.
From the documentation:
> When an object (table, index, trigger, > or view) is dropped from the database, > it leaves behind empty space. This > empty space will be reused the next > time new information is added to the > database. But in the meantime, the > database file might be larger than > strictly necessary. Also, frequent > inserts, updates, and deletes can > cause the information in the database > to become fragmented - scrattered out > all across the database file rather > than clustered together in one place. > > The VACUUM command cleans the main > database by copying its contents to a > temporary database file and reloading > the original database file from the > copy. This eliminates free pages, > aligns table data to be contiguous, > and otherwise cleans up the database > file structure.
Solution 2 - Sqlite
You can do this
$db->exec("DELETE FROM table");
$db->exec("vacuum");
and the file size will be changed.
Solution 3 - Sqlite
Cleaning Databases SQLite has two commands designed for cleaning—reindex and vacuum.
reindex is used to rebuild indexes. It has two forms:
reindex collation_name;
reindex table_name|index_name;
vacuum has the form:
VACUUM;