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. 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.
Thanks.
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
