Hi, Sir, I have only one auto increment primary key. Since the new rows will always have a higher number will the pages which have some free space by deleting rows with lower numbered keys never be reused ? e.g. If row with ROWID "1" was deleted and freed, will it not be used to store the NEW row which will be assigned ROWID 10001 ?
On Sat, Feb 8, 2014 at 11:38 PM, Richard Hipp <d...@sqlite.org> wrote: > 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 > _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
