On Tue, Sep 8, 2009 at 10:48 PM, Benjamin Rutt <rut...@osu.edu> wrote:
> On Mon, Sep 7, 2009 at 12:28 PM, P Kishor <punk.k...@gmail.com> wrote:
>
>> Find out if the DELETEion is chewing up the memory or the SELECTion. Try
>>
>> SELECT * FROM old_conns WHERE "end" >= strftime('%s', 'now', '-7 days');
>>
>> If the above is quick, you can simply create a new table with that,
>> and then drop the old table.
>>
>> CREATE TABLE new_conns AS SELECT * FROM old_conns WHERE "end" >=
>> strftime('%s', 'now', '-7 days');
>>
>> DROP TABLE old_conns;
>>
>
> When I do the select as you suggested, the process remains contained to a
> small amount of memory, which is good, but the result set from the select is
> huge. As I wrote originally, about 50% of the table would be deleted by my
> delete, so about 50% of the table would be selected were I to use your
> select. So yes, I could create a tmp table and insert into it, add the
> missing index, drop the old table, and rename the old to the new.
Well, I would recreate indexes as the last step in the process, after
I have dropped the old table. Indexes are probably significant in
size.
> This
> would work, but seems to me is quite an expensive hack. It would also
> require 200% of the original table space on disk in the worst case, and may
> necessitate an extra vacuum operation after the fact to conserve disk space
> (the original db file is 8GB so I suspect it would double in size to 16GB in
> the worst case). So it is a workaround, but not a cheap one.
Yes, but I am not sure if you actually tried the entire process and
measured the space and time tradeoffs. From your statements above, it
seems you haven't yet tried it since you write, "I could create a tmp
table..."
I would be curious to see if you get a decent space time tradeoff or
not. It may or may not be worth it.
>
> Thanks.
> _______________________________________________
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
--
Puneet Kishor
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
