Re: [sqlite] Speeding up the simplest queries

2008-09-22 Thread Igor Tandetnik
"Zbigniew Baniewski" <[EMAIL PROTECTED]> wrote in message news:[EMAIL PROTECTED] > On Mon, Sep 22, 2008 at 09:42:56PM +0100, Seun Osewa wrote: > >> You need FTS3: >> http://www.sqlite.org/cvstrac/wiki?p=FtsUsage >> It comes with the command line version of SQLite 3.6 > > You mean: it won't work u

Re: [sqlite] Speeding up the simplest queries

2008-09-22 Thread P Kishor
On 9/22/08, Zbigniew Baniewski <[EMAIL PROTECTED]> wrote: > On Mon, Sep 22, 2008 at 12:17:44PM +1000, BareFeet wrote: > > > select exists (select 1 from MyTable where condition) > > > > which will return a boolean result, and stop scanning the table after > > the first match is found. > > > Yes

Re: [sqlite] Speeding up the simplest queries

2008-09-22 Thread Zbigniew Baniewski
On Mon, Sep 22, 2008 at 09:42:56PM +0100, Seun Osewa wrote: > You need FTS3: > http://www.sqlite.org/cvstrac/wiki?p=FtsUsage > It comes with the command line version of SQLite 3.6 You mean: it won't work using SQLite's module for TCL? -- pozdrawiam / regards

Re: [sqlite] Speeding up the simplest queries

2008-09-22 Thread Seun Osewa
You need FTS3: http://www.sqlite.org/cvstrac/wiki?p=FtsUsage It comes with the command line version of SQLite 3.6 On Mon, Sep 22, 2008 at 8:43 PM, Zbigniew Baniewski <[EMAIL PROTECTED]> wrote: > On Sun, Sep 21, 2008 at 07:50:23PM -0400, Igor Tandetnik wrote: > > > > although not always I want >

Re: [sqlite] Speeding up the simplest queries

2008-09-22 Thread Zbigniew Baniewski
On Sun, Sep 21, 2008 at 07:50:23PM -0400, Igor Tandetnik wrote: > > although not always I want > > to fetch all that data. Sometimes I would just to count it. > > Don't call sqlite3_column_*. Just call sqlite3_step in a loop. But > again, if you want to get a count of records, you are unlikely t

Re: [sqlite] Speeding up the simplest queries

2008-09-22 Thread Zbigniew Baniewski
On Sun, Sep 21, 2008 at 07:07:33PM -0500, Jay A. Kreibich wrote: > It sounds like you need to take a more general approach to speeding > up your queries. If you've not yet looked at building appropriate > indexes, that seems like a good place to start. Right, proper indexing gives signific

Re: [sqlite] Speeding up the simplest queries

2008-09-22 Thread Zbigniew Baniewski
On Mon, Sep 22, 2008 at 12:17:44PM +1000, BareFeet wrote: > select exists (select 1 from MyTable where condition) > > which will return a boolean result, and stop scanning the table after > the first match is found. Yes, thanks - that's right: it is partial solution indeed. "Partial" - because

Re: [sqlite] Speeding up the simplest queries

2008-09-21 Thread BareFeet
>> I was using count(*) just to detect the presence of any record >> meeting given condition. Is it possible to make >> it fast _not_ using count(*)? I need just a "boolean result" of 1/0 >> (yes, there is at least one / there aren't any). >> > Add limit 1 to the query, so that it sto

Re: [sqlite] Speeding up the simplest queries

2008-09-21 Thread Gerry Snyder
Zbigniew Baniewski wrote: > On Sun, Sep 21, 2008 at 05:22:46PM -0500, Jay A. Kreibich wrote: > > >> count(*) is an odd one... In most database systems it is extremely >> fast, but in SQLite it tends to be rather slow. >> > > I forgot the important thing: usually I was using count(*) ju

Re: [sqlite] Speeding up the simplest queries

2008-09-21 Thread Jay A. Kreibich
On Mon, Sep 22, 2008 at 01:13:30AM +0200, Zbigniew Baniewski scratched on the wall: > On Sun, Sep 21, 2008 at 05:22:46PM -0500, Jay A. Kreibich wrote: > > > count(*) is an odd one... In most database systems it is extremely > > fast, but in SQLite it tends to be rather slow. > > [..] > >

Re: [sqlite] Speeding up the simplest queries

2008-09-21 Thread Igor Tandetnik
"Zbigniew Baniewski" <[EMAIL PROTECTED]> wrote in message news:[EMAIL PROTECTED] > The problem is, that it's not only about global number of records - I > would to have a possibility to quickly count number of records found > by conditional queries like: "select count(*) from table where > ". Parti

Re: [sqlite] Speeding up the simplest queries

2008-09-21 Thread Zbigniew Baniewski
On Sun, Sep 21, 2008 at 05:22:46PM -0500, Jay A. Kreibich wrote: > count(*) is an odd one... In most database systems it is extremely > fast, but in SQLite it tends to be rather slow. I forgot the important thing: usually I was using count(*) just to detect the presence of any record meeting

Re: [sqlite] Speeding up the simplest queries

2008-09-21 Thread Zbigniew Baniewski
On Sun, Sep 21, 2008 at 05:22:46PM -0500, Jay A. Kreibich wrote: > count(*) is an odd one... In most database systems it is extremely > fast, but in SQLite it tends to be rather slow. > [..] > If you search the archives, you'll find many discussions on the best > way to create a system

Re: [sqlite] Speeding up the simplest queries

2008-09-21 Thread Jay A. Kreibich
On Sun, Sep 21, 2008 at 11:32:18PM +0200, Zbigniew Baniewski scratched on the wall: > I've created a test database with almost one million records (about 20 fields > each). I noticed, that the simple query like "select count(*) from table" > takes about 10 seconds (the database file is of about 30

[sqlite] Speeding up the simplest queries

2008-09-21 Thread Zbigniew Baniewski
I've created a test database with almost one million records (about 20 fields each). I noticed, that the simple query like "select count(*) from table" takes about 10 seconds (the database file is of about 300 MB size). I'm wondering: is it the limit - or is it still possible to reduce the respons