On Thu, Dec 11, 2014 at 11:27 AM, Dominique Devienne
wrote:
>
> So why couldn't sqlite using the PK index to reduce the IO when doing a
> "select count(*) from t_with_non_int_pk", to avoid scanning the table?
>
It does.
--
D. Richard Hipp
d...@sqlite.org
, 2014 11:27 AM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] Select count(*)
On Thu, Dec 11, 2014 at 5:03 PM, Gabriel Corneanu wrote:
> I asked a similar question some time ago...
> See here:
> https://www.mail-archive.com/sqlite-users@sqlite.org/msg77488.html
>>
On Thu, Dec 11, 2014 at 5:03 PM, Gabriel Corneanu wrote:
> I asked a similar question some time ago...
> See here:
> https://www.mail-archive.com/sqlite-users@sqlite.org/msg77488.html
>> not a problem for rowid/pk (which are not allowed to be NULL), but it
>> matters a lot in the general case.
I asked a similar question some time ago...
See here:
https://www.mail-archive.com/sqlite-users@sqlite.org/msg77488.html
Gabriel
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> Message: 10
> Date: Wed, 19 Feb 2014 09:11:27 -0500
> From: Igor Tandetnik
>
> select strftime(...) as year, sum(ISO=100)
> FROM exifdata GROUP BY year ORDER BY year;
>
> --
>
> Message: 11
> Date: Wed, 19 Feb 2014 09:16:56 -0500
> From: Richard Hipp
> Maybe this
On Wed, Feb 19, 2014 at 9:04 AM, Patrick Proniewski wrote:
> Hello,
>
> I'm currently designing a small tool with shell scripts, sqlite3 backend,
> and gnuplot for graphical output.
> My SQLite database has one table, each row being a picture name and a
> subset of its EXIF tags (date, ISO, meteri
On 2/19/2014 9:04 AM, Patrick Proniewski wrote:
For ISO = 100, I issue this request:
SELECT strftime("%Y", datetime(CreateDate,"unixepoch")) as year, COUNT(ISO)
FROM exifdata WHERE ISO = 100 AND year not null GROUP BY ISO, year ORDER BY year;
That is correct, but not enough: the database inclu
Hello,
I'm currently designing a small tool with shell scripts, sqlite3 backend, and
gnuplot for graphical output.
My SQLite database has one table, each row being a picture name and a subset of
its EXIF tags (date, ISO, metering mode...).
In order to process data into gnuplot, I use SELECT to o
Maybe select count(*) where ...?
Alessandro
Il giorno 19/ago/2013 11:38, "techi eth" ha scritto:
> Hi,
>
> What is the way to get number of possible count matching with criteria for
> SELECT?
>
> SELECT Colum from Table WHERE Criteria
>
> I tried with sqlite3_count_changes() & sqlite3_data_co
Hi,
What is the way to get number of possible count matching with criteria for
SELECT?
SELECT Colum from Table WHERE Criteria
I tried with sqlite3_count_changes() & sqlite3_data_count() function but
they are not returning actual value.
Definitely getting Colum count by sqlite3_coloum_count () &
On Fri, Jun 7, 2013 at 11:51 AM, Ryan Johnson
wrote:
> On 06/06/2013 10:52 AM, Gabriel Corneanu wrote:
>
>> In my opinion, count(*) is the same as count(rowid) (I see that even
>> count() is accepted); I could say it's even the same as count(x) (any
>> other
>> field).
>>
> Not quite... count(x) o
On 06/06/2013 10:52 AM, Gabriel Corneanu wrote:
In my opinion, count(*) is the same as count(rowid) (I see that even
count() is accepted); I could say it's even the same as count(x) (any other
field).
Not quite... count(x) only counts rows having non-NULL x. Granted,
that's not a problem for row
On Fri, 07 Jun 2013 13:12:14 +0200
Clemens Ladisch wrote:
> Eduardo Morras wrote:
> > where t.a = NULL
>
> where t.a IS NULL
>
> (NULL compares as not equal to any value, including itself.)
OPppss you're right. Thought too fast and wrote even faster :(
>
> Regards,
> Clemens
--- ---
Edu
Eduardo Morras wrote:
> where t.a = NULL
where t.a IS NULL
(NULL compares as not equal to any value, including itself.)
Regards,
Clemens
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-use
On Thu, 6 Jun 2013 10:53:55 -0400
Richard Hipp wrote:
> On Thu, Jun 6, 2013 at 10:52 AM, Gabriel Corneanu > wrote:
>
> > Strange is, count(*) uses the cover index for a but "select count(a)" does
> > NOT use the same cover index...
> >
>
> count(a) has to check for NULL values of a, which are
This is not at all my case ...
I don't obviously write 1 by 1, but using blocks of data ( array of
struct ), virtual tables wrappers, and "insert ... select".
This way I can achieve >200k rec/s, or at least 100k when having some
more fields.
Right now I'm completely CPU bound, it's 100% load at
On 7 Jun 2013, at 8:57am, Gabriel Corneanu wrote:
> BTW I found this by opening some file over network, which of course made
> everything worse.
> [...]
> Not that I really need, but I have to support specified data rates up to 100k
> records / second.
Maximum speed of a SQLite database is us
I admit I didn't think (or didn't even read in detail) about technical
implementation.
This is an extract from analyzer:
*** Table AE_DATA
Percentage of total database.. 99.89%
Number of entries. 1030371
Bytes of
On 6/6/2013 11:26 AM, Gabriel Corneanu wrote:
Again sorry for count(a), I wrote too fast. I understand of course about
null values.
Otherwise by rowid I mean the autogenerated primary key. In my actual
case, I have a field as alias.
CREATE TABLE t(id integer primary key, a);
explain query plan
On Thu, Jun 6, 2013 at 11:26 AM, Gabriel Corneanu wrote:
>
> 2. Is there NO WAY to quickly get the row count WITHOUT full scan if I
> only have the auto primary key??
>
The b-tree structures in the SQLite file format do not store the row count,
as that slows down writes (since the row count woul
Gabriel Corneanu wrote:
> I was surprised to find that simple query "select count(*) from table" took
> too much time, and found that it does NOT use the primary key index??
In SQLite, indexes are stored as B-trees, ordered by the indexed columns.
Tables are _also_ stored as B-trees, ordered by t
Again sorry for count(a), I wrote too fast. I understand of course about
null values.
Otherwise by rowid I mean the autogenerated primary key. In my actual
case, I have a field as alias.
CREATE TABLE t(id integer primary key, a);
explain query plan select count(*) from t -> scan table
create
OK I understand, then it remains the question why it does not use the
primary key??
Thanks,
Gabriel
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
On Thu, Jun 06, 2013 at 04:52:12PM +0200, Gabriel Corneanu scratched on the
wall:
> I was surprised to find that simple query "select count(*) from table" took
> too much time, and found that it does NOT use the primary key index??
> e.g.
> CREATE TABLE t(a);
> explain query plan select count(*) f
On Thu, Jun 6, 2013 at 10:52 AM, Gabriel Corneanu wrote:
> Strange is, count(*) uses the cover index for a but "select count(a)" does
> NOT use the same cover index...
>
count(a) has to check for NULL values of a, which are not counted.
count(*) does not.
--
D. Richard Hipp
d...@sqlite.org
__
I was surprised to find that simple query "select count(*) from table" took
too much time, and found that it does NOT use the primary key index??
e.g.
CREATE TABLE t(a);
explain query plan select count(*) from t
I get : SCAN TABLE t (~100 rows)
If I use CREATE TABLE t(a unique), then it uses
t;
>
>
> From: Patrik Nilsson
> To: General Discussion of SQLite Database
> Sent: Saturday, June 23, 2012 12:24 PM
> Subject: [sqlite] select count
>
> Hi All,
>
> I have a table "a" which contains number like:
>
&g
PM
Subject: [sqlite] select count
Hi All,
I have a table "a" which contains number like:
1
1
1
1
2
2
4
When I "select distinct * from a" I get:
1
2
4
How can I get the count for each number? Like this:
1|4
2|2
4|1
How do I write the needed select st
Hi All,
I have a table "a" which contains number like:
1
1
1
1
2
2
4
When I "select distinct * from a" I get:
1
2
4
How can I get the count for each number? Like this:
1|4
2|2
4|1
How do I write the needed select statement?
Best regards,
Patrik
___
GNU C (Sourcery CodeBench 2011.03-95) version 4.5.2 (mips-linux-gnu)
compiled by GNU C version 4.3.2, GMP version 4.3.2, MPFR version
2.4.2, MPC version 0.8.1
On Mon, Mar 5, 2012 at 6:12 PM, Richard Hipp wrote:
> On Mon, Mar 5, 2012 at 7:04 AM, Sreekumar TP
> wrote:
>
> > The crash is
On March 5, Richard Hipp wrote:
On Mon, Mar 5, 2012 at 1:39 PM, Larry Brasfield
wrote:
> On March 5, Richard Hipp wrote:
>
> On Mon, Mar 5, 2012 at 7:04 AM, Sreekumar TP
>> wrote:
>>
>> > The crash is a result of -O3 flag used during compiling the library.
>> >
>>
>> A compiler bug, then. OK
On Mon, Mar 5, 2012 at 1:39 PM, Larry Brasfield
wrote:
> On March 5, Richard Hipp wrote:
>
> On Mon, Mar 5, 2012 at 7:04 AM, Sreekumar TP
>> wrote:
>>
>> > The crash is a result of -O3 flag used during compiling the library.
>> >
>>
>> A compiler bug, then. OK. Good to know. SQLite has previo
On March 5, Richard Hipp wrote:
On Mon, Mar 5, 2012 at 7:04 AM, Sreekumar TP wrote:
> The crash is a result of -O3 flag used during compiling the library.
>
A compiler bug, then. OK. Good to know. SQLite has previously uncovered
bugs in GCC, MSVC, and LLVM. Which compiler are you using?
On Mon, Mar 5, 2012 at 7:04 AM, Sreekumar TP wrote:
> The crash is a result of -O3 flag used during compiling the library.
>
A compiler bug, then. OK. Good to know. SQLite has previously uncovered
bugs in GCC, MSVC, and LLVM. Which compiler are you using?
>
> -Sreekumar
>
>
>
> On Sat, Ma
The crash is a result of -O3 flag used during compiling the library.
-Sreekumar
On Sat, Mar 3, 2012 at 2:00 PM, Sreekumar TP wrote:
> Its does not crash on x86. I havent built the shell for my mips device.
>
> Sreekumar
> On Mar 3, 2012 10:26 AM, "Dan Kennedy" wrote:
>
>> On 03/03/2012 10:3
Its does not crash on x86. I havent built the shell for my mips device.
Sreekumar
On Mar 3, 2012 10:26 AM, "Dan Kennedy" wrote:
> On 03/03/2012 10:30 AM, Sreekumar TP wrote:
>
>> Could someone throw some light on this issue too?
>>
>
> I can't see from the stack trace why this is crashing.
>
> D
On 03/03/2012 10:30 AM, Sreekumar TP wrote:
Could someone throw some light on this issue too?
I can't see from the stack trace why this is crashing.
Does it crash if you run the query from the sqlite shell?
Maybe try building the shell without optimizations, and
then running it under valgrind
Could someone throw some light on this issue too?
Sreekumar
On Mar 2, 2012 10:05 AM, "Sreekumar TP" wrote:
> The backtrace
> ===
>
>
> Program received signal SIGSEGV, Segmentation fault.
> 0x2b657288 in sqlite3Parser (yyp=0x2d401e40, yymajor=119, yyminor=...,
> pParse=0x2d4035c8) at
The backtrace
===
Program received signal SIGSEGV, Segmentation fault.
0x2b657288 in sqlite3Parser (yyp=0x2d401e40, yymajor=119, yyminor=...,
pParse=0x2d4035c8) at sqlite3.c:106740
106740 }while( yymajor!=YYNOCODE && yypParser->yyidx>=0 );
#0 0x2b657288 in sqlite3Parser (yyp=0x2
yes, will post the trace.
Sreekumar
On Mar 1, 2012 11:42 PM, "Dan Kennedy" wrote:
> On 03/01/2012 10:54 PM, Sreekumar TP wrote:
>
>> version 3.7.7.1
>> The query works on x86, but fails on MIPS processor!
>>
>
> Are you able to post a stack trace? Ideally generated by
> the gdb "where full" comm
On 03/01/2012 10:54 PM, Sreekumar TP wrote:
version 3.7.7.1
The query works on x86, but fails on MIPS processor!
Are you able to post a stack trace? Ideally generated by
the gdb "where full" command. Thanks.
Dan.
Sreekumar
On Thu, Mar 1, 2012 at 7:00 PM, Dan Kennedy wrote:
On 03/01/
version 3.7.7.1
The query works on x86, but fails on MIPS processor!
Sreekumar
On Thu, Mar 1, 2012 at 7:00 PM, Dan Kennedy wrote:
> On 03/01/2012 05:48 PM, Sreekumar TP wrote:
>
>> In my system, the statement causes sqlite3parser function to crash.
>> My compiler is mips , little endian, gc
On 03/01/2012 05:48 PM, Sreekumar TP wrote:
In my system, the statement causes sqlite3parser function to crash.
My compiler is mips , little endian, gcc version is 4.5.2
SQLite version? 3.7.10 seems Ok here.
Dan.
___
sqlite-users mailing list
sqlite
In my system, the statement causes sqlite3parser function to crash.
My compiler is mips , little endian, gcc version is 4.5.2
-Sreekumar
On Thu, Mar 1, 2012 at 2:59 PM, Simon Davies
wrote:
> On 1 March 2012 09:22, Sreekumar TP wrote:
> > doesnt work either.
> >
>
> In what way does it fail
On 1 March 2012 09:34, Petite Abeille wrote:
>
> On Mar 1, 2012, at 10:29 AM, Simon Davies wrote:
>
>>> doesnt work either.
>>>
>>
>> In what way does it fail to live up to your expectations?
>
> Simon? Are you impersonating Igor?!? :D
>
LOL :-)
___
sql
On Mar 1, 2012, at 10:29 AM, Simon Davies wrote:
>> doesnt work either.
>>
>
> In what way does it fail to live up to your expectations?
Simon? Are you impersonating Igor?!? :D
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:
On 1 March 2012 09:22, Sreekumar TP wrote:
> doesnt work either.
>
In what way does it fail to live up to your expectations?
Regards,
Simon
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-use
On Mar 1, 2012, at 10:22 AM, Sreekumar TP wrote:
> doesnt work either.
Sure it does.
sqlite> select count( distinct name ) from header;
899
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/
doesnt work either.
On Thu, Mar 1, 2012 at 2:50 PM, Petite Abeille wrote:
>
> On Mar 1, 2012, at 9:46 AM, Sreekumar TP wrote:
>
> > Is SELECT COUNT (DISTINCT column_name FROM table) syntax supported ?
>
> try:
>
> select count( distinct foo ) from bar
>
> __
On Mar 1, 2012, at 9:46 AM, Sreekumar TP wrote:
> Is SELECT COUNT (DISTINCT column_name FROM table) syntax supported ?
try:
select count( distinct foo ) from bar
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/ma
Hi,
Is SELECT COUNT (DISTINCT column_name FROM table) syntax supported ?
The above causes a crash in sqlite3Parser function..
-Regards,
Sreekumar
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sql
-Original Message-
From: Dennis Povshedny [mailto:[EMAIL PROTECTED]
Sent: Tuesday, September 25, 2007 4:40 PM
To: sqlite-users@sqlite.org
Subject: RE: [sqlite] select COUNT (DISTINCT column1, column2) from
table?
Hi Phani!
For your sample the following query will fit:
select COUNT (DISTINCT y
Seems useful
Best Regards,
A.Sreedhar.
-Original Message-
From: Dennis Povshedny [mailto:[EMAIL PROTECTED]
Sent: Tuesday, September 25, 2007 4:40 PM
To: sqlite-users@sqlite.org
Subject: RE: [sqlite] select COUNT (DISTINCT column1, column2) from table?
Hi Phani!
For your sample the
PROTECTED]
sip:[EMAIL PROTECTED]
-Original Message-
From: B V, Phanisekhar [mailto:[EMAIL PROTECTED]
Sent: Tuesday, September 25, 2007 2:46 PM
To: sqlite-users@sqlite.org
Subject: RE: [sqlite] select COUNT (DISTINCT column1, column2) from
table?
Hi Simon,
Yeah, I thought of the query which u
Phani
-Original Message-
From: Simon Davies [mailto:[EMAIL PROTECTED]
Sent: Tuesday, September 25, 2007 4:09 PM
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] select COUNT (DISTINCT column1, column2) from
table?
On 25/09/2007, B V, Phanisekhar <[EMAIL PROTECTED]> wrote:
> Hi Sim
s (3, 2006, 5);
sqlite> insert into m values (4, 2004, 4);
sqlite> SELECT COUNT(*) FROM ( SELECT COUNT(*) FROM m group by year,month);
3
sqlite>
Regards,
Bharath
On 9/25/07 3:59 PM, "B V, Phanisekhar" <[EMAIL PROTECTED]> wrote:
> Hi Simon,
>
> Assum
for instructions
sqlite>
sqlite> create table m( mNo integer, year integer, month integer );
sqlite>
sqlite> insert into m values (1, 2006, 11 );
sqlite> insert into m values (2, 2007, 5 );
sqlite> insert into m values (3, 2007, 5 );
sqlite> insert into m values (4, 2007, 6 );
sq
sqlite>
sqlite> create table m( mNo integer, year integer, month integer );
sqlite>
sqlite> insert into m values (1, 2006, 11 );
sqlite> insert into m values (2, 2007, 5 );
sqlite> insert into m values (3, 2007, 5 );
sqlite>
s
nteger, year integer, month integer );
sqlite>
sqlite> insert into m values (1, 2006, 11 );
sqlite> insert into m values (2, 2007, 5 );
sqlite> insert into m values (3, 2007, 5 );
sqlite>
sqlite> select count(*), year, month from m group by year,month;
1|2006|11
2|2007|5
sqlite&g
Assume I have a database.
Maintable (matchNo integer, year INTEGER, month INTEGER)
I have to find the count of distinct year, month combinations in which
matches were played.
I tried the query select COUNT (DISTINCT column1, column2) from table
but this gives an error.
I would like to know i
you should change this:
rc = sqlite3_exec(memdb, "insert into dummy values (\"dummy\", \"user\")",
to this:
rc = sqlite3_exec(memdb, "insert into dummy values ('dummy', 'user')",
You need single quotes for strings
On 5/31/06, [EMAIL PROTECTED] <[EMAIL PROTECTED]> wrote:
I am pretty sure it i
[EMAIL PROTECTED] wrote:
I am pretty sure it is something trivial but it does not work for me, can
someone please help me figure out what I may be missing.
strcpy( query, "select count(*) from dummy");
rc = sqlite3_prepare( memdb, query, strlen(query), &pStmt, NULL);
if (rc) {
return;
I am pretty sure it is something trivial but it does not work for me, can
someone please help me figure out what I may be missing.
What happens is that the call to sqlite3_bind_text returns error 25
(SQLITE_RANGE). This is caused because p->nVar is set to 0 and I am
passing 1.
How can I know ho
On 5/22/06, Danilo <[EMAIL PROTECTED]> wrote:
Hi to all,
I am looking for the way of memorizing the result of:
SELECT count(field1) FROM Table1 WHERE field1 LIKE 'xyz%';
in a int variable, without a callback or sqlite3_prepare-SQLITE_SCHEMA
in C++ program.
Is there a way to do it?
look at sql
Hi to all,
I am looking for the way of memorizing the result of:
SELECT count(field1) FROM Table1 WHERE field1 LIKE 'xyz%';
in a int variable, without a callback or sqlite3_prepare-SQLITE_SCHEMA
in C++ program.
Is there a way to do it?
Regards, Danilo.
Home Page: http://www.digitazero.org
luned
65 matches
Mail list logo
