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
16 matches
Mail list logo