Estimating impact of indexes SQLite database size

indexsize;sqlite

I'm trying to estimate the database size (on disk) for a SQLite DB that includes a number of indexed columns. These columns are of (SQLite) type Integer and String. It's straightforward enough to estimate the size per row with these columns, but I'm not sure how to account for the extra per-row padding due to the indexes. What's the best approach for this?

Best Answer

At least some of the details of index B-trees are documented.

It might be easier to just create a test database, populate it, and measure the file size before and after creating an index.

Related Solutions

How many disk seeks does it take to write one row on SQLite

This question was answered on Stackoverflow.

By default its one transaction per disk rotation (roughly),

http://www.sqlite.org/faq.html#q19

The way to speedier writes is to wrap multiple ones inside a transaction.

Sql-server – SQL Server Table Size Discrepancy

When you delete from heaps, allocated space may not be unallocated unless you use a table lock. See "Deleting Rows from a Heap" from DELETE on MSDN

This is a separate problem to fragmentation (which happens of course)

Just in case, have you tried this to update usage info?

EXEC sp_spaceused 'MySchema.MyTable', 'true'

With no indexes or keys, you can't defragment it normally.
You can add/drop a clustered index, or do something like this

SELECT * INTO [MySchema].[MyTableNew] 
FROM [MySchema].[MyTable]
-- ORDER BY something

-- if you like
EXEC sp_spaceused 'MySchema.MyTableNew', 'true'

DROP TABLE [MySchema].[MyTable];

EXEC sp_rename 'MySchema.MyTableNew', 'MyTable';

oops: just seen updates, my Internets has been broken on and off...

Best Answer

Related Solutions

How many disk seeks does it take to write one row on SQLite

Sql-server – SQL Server Table Size Discrepancy

Related Question