Thanks Kees. My app still needs some optimizations, your suggestions look good.
One question though: what will happen when id reaches 2^32 - 1 ? (This app is going to log a lot of events, 24/7 for a long time, so it's not a purely theoretical question.) Kees Nuyt wrote: > > On Fri, 4 Sep 2009 06:43:05 -0700 (PDT), wlof > <wlo...@gmail.com> 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 > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > > -- View this message in context: http://www.nabble.com/Slow-SELECT-query-tp25294597p25435503.html Sent from the SQLite mailing list archive at Nabble.com. _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
