Re: [sqlite] COUNT very slow

2010-09-24 Thread Michele Pradella
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 th

Re: [sqlite] COUNT very slow

2010-09-24 Thread Martin Engelschalk
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 a

Re: [sqlite] COUNT very slow

2010-09-24 Thread 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 Il 24/09/2010 10.29, Martin Engelschalk ha scritto: >Hello Michele, > > sqlit

Re: [sqlite] COUNT very slow

2010-09-24 Thread Drake Wilson
Quoth Michele Pradella , on 2010-09-24 10:13:59 +0200: > 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

Re: [sqlite] COUNT very slow

2010-09-24 Thread Martin Engelschalk
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.htm

Re: [sqlite] COUNT very slow

2010-09-24 Thread Dan Kennedy
On Sep 24, 2010, at 3:13 PM, Michele Pradella wrote: > 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! In SQLite, count() is obliged to traverse the entire table

Re: [sqlite] COUNT very slow

2010-09-24 Thread Michele Pradella
I forgot to tell you that I'm using the sqlite3 shell to test the statement, and I got same slowness with DB in use or DB not in use Il 24/09/2010 10.13, Michele Pradella ha scritto: >I have an SQLite DB of about 9GB with about 2.500.000 records. > I can't understand why the "select COUNT(

[sqlite] COUNT very slow

2010-09-24 Thread 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?