Am 24.09.2010 10:38, schrieb Michele Pradella: > ok, thank you for the advices, I'll try to use a TRIGGER. > The DB already has an index. > Anyway if I have to count something like this: > select COUNT(*) from logs WHERE DateTime<=yesterday > I can't do it with a TRIGGER No, but in this case an index on DateTime will help (except when most of the records are older than yesterday). Also, you could keep track of the number of records for each day with a table containing DateTime and RecordCount.
> Il 24/09/2010 10.29, Martin Engelschalk ha scritto: >> Hello Michele, >> >> sqlite does not remember the number of records in a table. Therefore, >> counting them requires to scan the full table, which explains the slow >> perfornamce. >> >> This topic has been discussed previously in this list. See >> http://www.mail-archive.com/sqlite-users@sqlite.org/msg10279.html >> >> If you need the result quickly, you have to maintain the rnumber of >> records yourself in a different table, perhaps using triggers. >> >> Martin >> >> >> Am 24.09.2010 10:13, schrieb Michele Pradella: >>> I have an SQLite DB of about 9GB with about 2.500.000 records. >>> I can't understand why the "select COUNT(*) from log" statement is >>> extremely slow, it takes me about 9-10 minutes! >>> I try with: >>> select COUNT(1) from logs >>> select COUNT(DateTime) from logs >>> same result. Have you idea of why it's so slow? >>> _______________________________________________ >>> sqlite-users mailing list >>> sqlite-users@sqlite.org >>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users >> _______________________________________________ >> sqlite-users mailing list >> sqlite-users@sqlite.org >> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users >> >> _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
