On Fri, Feb 7, 2014 at 7:39 AM, Raheel Gupta <raheel...@gmail.com> wrote:
> Hi, > > My Page size is 64KB and I store around 4KB of row data in one row. > I store around 10000 rows in one table and the database size reaches 42MB. > > Now, I am facing a peculiar problem. When I delete just 2-3 rows, that page > is not reused for the new data which will be inserted in the future. > That space will be reused if your new data has the same (or similar) key as the rows that were deleted. In order to achieve fast lookup, content must be logically ordered by key. That means that all of the rows on a single page must have keys that are close to one another. If you have space on a page, and you insert a new row with a nearby key, that space will be (re)used. But if you insert a new row with a very different key, that new row must be placed on a page close to other rows with similar keys, and cannot appear on the same page with rows of very dissimilar keys. > > The pragma freelist_count shows 0 if I delete the 1st 10 rows (approx 40KB) > Only if I delete more than 20 rows does the freelist_count reflect 1 page > as free. > > How should I get SQLIte to use the free space within a partially used page > when rows from that page have been deleted. > > This causes a lot of space wastage when I store more rows. > _______________________________________________ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > -- D. Richard Hipp d...@sqlite.org _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
