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 ? (integer /1/2/4/6
bytes )?? REAL take 8 bytes...
3) login app logic changes:
to log:
a. select max(timestamp), id from log_table
b. id=id+1
c. id=(id modulo 10000)
d. instead of INSERT use replace with PK Id....
--- El vie 4-sep-09, wlof <wlo...@gmail.com> escribió:
> De: wlof <wlo...@gmail.com>
> Asunto: [sqlite] Slow SELECT query
> Para: sqlite-users@sqlite.org
> Fecha: viernes, 4 de septiembre de 2009, 10:43 am
>
> 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?
>
> --
> View this message in context:
> http://www.nabble.com/Slow-SELECT-query-tp25294597p25294597.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
>
Yahoo! Cocina
Encontra las mejores recetas con Yahoo! Cocina.
http://ar.mujer.yahoo.com/cocina/
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
