Search code examples
c++sqlitesql-delete

Sqlite3/C++ executes DELETE statement without changing the db size

How is it possible? I have a simple C++ app that is using SQLite3 to INSERT/DELETE records. I use a single database and a single table inside. Then after I choose to store some data into the db, it does and the size of my.db increases naturally.

While there is a problem with DELETE - it does not. But if I do:

sqlite3 my.db
sqlite> select count(*) from mytable;

there is 0 returned which is okay, but if do ls -l on the folder containing my.db, the size is the same.

Can anybody explain?

Solution

  • When you execute a DELETE query, Sqlite does not actually delete the records and rearrange the data. That would take too much time. Instead, it just marks deleted records and ignore them from then on.

    If you actually want to reduce the data size, execute VACUUM command. There is also an option for auto vacuuming. See http://www.sqlite.org/lang_vacuum.html.