Hello
w I don't want the log to grow indefinitely, so I periodically call a method
w which makes the following query:
w SELECT id FROM log ORDER BY timestamp DESC LIMIT 10000,9999999
w A DELETE query is executed on every result.
How about something like:
DELETE FROM log
WHERE id IN (SELECT id
FROM log
ORDER BY timestamp DESC
LIMIT 10000,9999999);
Which will only be one delete statement, instead of thousands.
w I've added an index on the timestamp column:
w CREATE INDEX IF NOT EXISTS log_idx ON log (id, timestamp)
You should only need an index on the timestamp column for the SELECT
statement to work. The id column is already indexed as it is the primary
key.
I hope this helps.
Swithun.
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
