Re: [sqlite] Slow SELECT query

On Fri, 4 Sep 2009 06:43:05 -0700 (PDT), wlof 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 i

Re: [sqlite] SQLite 3.6.17

On Thu, Sep 3, 2009 at 10:30 PM, Nicolas Williams wrote: > On Thu, Sep 03, 2009 at 03:27:38PM +0100, Simon Slavin wrote: > > On 3 Sep 2009, at 1:38am, P Kishor wrote: > > > well, I think the problem is with the sqlite3 command line tool. > > I agree. I just checked it with OS X 10.6, which comes

Re: [sqlite] Slow SELECT query

Swithun Crowe wrote: > > How about something like: > > DELETE FROM log > WHERE id IN (SELECT id > FROM log > ORDER BY timestamp DESC > LIMIT 1,999); > > Which will only be one delete statement, instead of thousands. I

Re: [sqlite] Slow SELECT query

Sebastian Bermudez wrote: > > 1) > Ok, change the index to: > CREATE INDEX IF NOT EXISTS log_idx ON log (timestamp) > > column Id is not used for select... > the Id column just is needed for delete operation. > This seems to have done the trick. The SELECT query now takes ~15 ms. Much better :

Re: [sqlite] Slow SELECT query

1) Ok, change the index to: CREATE INDEX IF NOT EXISTS log_idx ON log (timestamp) column Id is not used for select... the Id column just is needed for delete operation. 2) what's log period your app.have ? Need Year ? Month ? Day ? can you change the REAL data type for an smaller one ? (integ

Re: [sqlite] Slow SELECT query

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 1,999 w A DELETE query is executed on every result. How about something like: DELETE FROM log WHERE id IN (SELEC

[sqlite] Slow SELECT query

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 foll

[sqlite] sqlite3_exec fails on arm

Hi, I am using sqlite3.6.17 on arm_v7 . sqlite3_Exec fails giving the following error sqlite3_exec malformed database schema error : unrecognized token "'". I am able to read the database manually using select commands. Executing the same sqlite3_exec statements on x86 machine on the same db file