On Fri, 4 Sep 2009 06:43:05 -0700 (PDT), wlof
<[email protected]> wrote:
>
>Hi everyone,
>
>I'm using SQLite to maintain a log of events in my application.
>
>There is only one table:
>CREATE TABLE IF NOT EXISTS log (id INTEGER PRIMARY KEY AUTOINCREMENT,
>timestamp REAL, event TEXT)
>
>I don't want the log to grow indefinitely, so I periodically call a method
>which makes the following query:
>SELECT id FROM log ORDER BY timestamp DESC LIMIT 10000,9999999
>
>A DELETE query is executed on every result.
>
>The problem is that, on a 1.3MB table (10000 entries), the SELECT query
>takes 12 seconds to be executed! This is way too long.
>
>I've added an index on the timestamp column:
>CREATE INDEX IF NOT EXISTS log_idx ON log (id, timestamp)
>
>But there is little to no performance improvement.
>
>What am I doing wrong?
Apparently you want to delete all log entries except the
last 10000, without caring about the value of the timestamp.
Your table already has a monotonically increasing primary
key (historical order). Maintaining an index on timestamp is
redundant and slows down inserts.
I would suggest:
DELETE FROM log
WHERE id < (
SELECT max(id) - 10000
FROM log
);
And if that isn't fast enough, you can even try:
DELETE FROM log
WHERE id < (
SELECT seq - 10000
FROM sqlite_sequence
WHERE name = 'log'
);
(untested)
--
( Kees Nuyt
)
c[_]
_______________________________________________
sqlite-users mailing list
[email protected]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
