I can get a big speed up of COUNT if I first do a VIEW of what I have to count and than make select COUNT on the view. Without VIEW: 9 Minutes With VIEW: 8 Seconds!
Il 24/09/2010 10.58, Martin Engelschalk ha scritto: > > 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 > > -- Selea s.r.l. Michele Pradella R&D SELEA s.r.l. Via Aldo Moro 69 Italy - 46019 Cicognara (MN) Tel +39 0375 889091 Fax +39 0375 889080 *michele.prade...@selea.com* <mailto:michele.prade...@selea.com> *http://www.selea.com* _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
