On 2015-10-02 07:28 PM, Bart Smissaert wrote:
>> if any single column in those two rows is NULL.
> OK, I got it and that will make it even less likely that this situation
> will occur.
> I think we got this exhausted now.
Sure - but don't be dismayed though, every opportunity for optimization
On 2015-10-02 05:41 PM, Bart Smissaert wrote:
>> you're just throwing random terms around and hoping something sticks.
> Not sure where you got that idea from, but let me explain better:
>
> Say we have a table:
>
> CREATE TABLE TABLE1
> ([FIELD1] INTEGER,
> [FIELD2] TEXT,
> [FIELD3] TEXT,
>
> Keep at it.
Will do.
RBS
On Fri, Oct 2, 2015 at 6:46 PM, R.Smith wrote:
>
>
> On 2015-10-02 07:28 PM, Bart Smissaert wrote:
>
>> if any single column in those two rows is NULL.
>>>
>> OK, I got it and that will make it even less likely that this situation
>> will occur.
>> I think we got
No, whether the column contains a null is irrelevant. It is whether the column
CAN contain a null.
> -Original Message-
> From: sqlite-users-bounces at mailinglists.sqlite.org [mailto:sqlite-users-
> bounces at mailinglists.sqlite.org] On Behalf Of Bart Smissaert
> Sent: Friday, 2
You are making an error.
sqlite> create table x(a, b, c, d);
sqlite> create unique index y on x(a,b,c,d);
sqlite> insert into x values(1,2,3,null);
sqlite> insert into x values(1,2,3,null);
sqlite> insert into x values(1,2,3,null);
sqlite> select * from x;
1|2|3|
1|2|3|
1|2|3|
sqlite> select
> if any single column in those two rows is NULL.
OK, I got it and that will make it even less likely that this situation
will occur.
I think we got this exhausted now.
RBS
On Fri, Oct 2, 2015 at 6:24 PM, Richard Hipp wrote:
> On 10/2/15, Bart Smissaert wrote:
> > Do you mean that is only
Yes, I can see that there is no point in SQLite having this optimization.
Of course I can do this optimization myself (just run select count(*) from
table, instead of using distinct) if I know all conditions are met. This
wasn't anything practical I needed, just a fleeting thought.
RBS
On Fri,
OK, thanks.
Do you mean that is only valid if there are no rows where all columns are
NULL?
In any case, I can see that this optimization (in SQLite) is just not worth
the trouble.
RBS
On Fri, Oct 2, 2015 at 6:02 PM, Richard Hipp wrote:
> On 10/2/15, Bart Smissaert wrote:
> >
> > Unless I am
On 2 Oct 2015, at 4:41pm, Bart Smissaert wrote:
> Say we have a table:
>
> CREATE TABLE TABLE1
> ([FIELD1] INTEGER,
> [FIELD2] TEXT,
> [FIELD3] TEXT,
> [FIELD4] REAL)
>
> and we have a unique index on all 4 fields:
>
> CREATE UNIQUE INDEX IDX1
> ON TABLE1
> (FIELD1, FIELD2, FIELD3, FIELD4)
> you're just throwing random terms around and hoping something sticks.
Not sure where you got that idea from, but let me explain better:
Say we have a table:
CREATE TABLE TABLE1
([FIELD1] INTEGER,
[FIELD2] TEXT,
[FIELD3] TEXT,
[FIELD4] REAL)
and we have a unique index on all 4 fields:
CREATE
On 2015-10-02 10:05 AM, Bart Smissaert wrote:
> Noticed that if I have table with a unique index on all fields, counting
> all rows is still a lot faster
> (about a factor 10 on my particular test table) than counting distinct rows.
> Could maybe an optimization be added to SQLite to speed this
It is faster because if it knows there is no where or join or whatever row
limiting condition and it also knows there is
a unique index on all fields it can simply do select count(rowid) from
table1 and not do any count distinct.
RBS
On Fri, Oct 2, 2015 at 3:51 PM, Scott Hess wrote:
> On Fri,
> The Uniqueness of the output depends on which fields are included, JOINs,
UNIONs, etc. etc.
I am not talking about that situation. I am only referring to a situation
where you want to count all
rows in a table. I know it will be uncommon to have an index on all fields
and this is not really a
Counting all rows vs counting distinct rows is a very different task.
In simple terms
For all rows the process is
read every leaf page in the index
query the cell count field (number of records)
add cell count to the to the total count
repeat for the remaining leaf pages
For distinct records
On 2 Oct 2015, at 9:05am, Bart Smissaert wrote:
> Noticed that if I have table with a unique index on all fields, counting
> all rows is still a lot faster
> (about a factor 10 on my particular test table) than counting distinct rows.
> Could maybe an optimization be added to SQLite to speed
On 10/2/15, Bart Smissaert wrote:
> Do you mean that is only valid if there are no rows where all columns are
> NULL?
No, I mean that two rows can be identical (not distinct) event if
there is a unique index on all columns, if any single column in those
two rows is NULL. Example:
SQLite
On 10/2/15, Bart Smissaert wrote:
>
> Unless I am mistaken here this is done with a SQL like this:
>
> SELECT COUNT(*) AS UNIQUE_ROWS
> FROM (SELECT DISTINCT * FROM TABLE1)
>
> But if we take advantage of the fact that this table has a unique index on
> all the fields of the table
> we can simply
On Fri, Oct 2, 2015 at 9:41 AM, Bart Smissaert
wrote:
> > you're just throwing random terms around and hoping something sticks.
>
> Not sure where you got that idea from, but let me explain better:
>
> Say we have a table:
>
> CREATE TABLE TABLE1
> ([FIELD1] INTEGER,
> [FIELD2] TEXT,
> [FIELD3]
On Fri, Oct 2, 2015 at 9:41 AM, Bart Smissaert
wrote:
> > you're just throwing random terms around and hoping something sticks.
>
> Not sure where you got that idea from, but let me explain better:
>
> Say we have a table:
>
> CREATE TABLE TABLE1
> ([FIELD1] INTEGER,
> [FIELD2] TEXT,
> [FIELD3]
On Fri, Oct 2, 2015 at 8:41 AM, Bart Smissaert
wrote:
> > you're just throwing random terms around and hoping something sticks.
>
> Not sure where you got that idea from, but let me explain better:
>
AFAICT this is the first posting where you said "I want to count all the
unique rows of this
Noticed that if I have table with a unique index on all fields, counting
all rows is still a lot faster
(about a factor 10 on my particular test table) than counting distinct rows.
Could maybe an optimization be added to SQLite to speed this up, taking
advantage of the fact that there is a unique
Why does any of that matter? SELECT COUNT(*) FROM table; already knows all
of that information.
If you have a question about why one query is faster/slower than another
query given one schema versus another schema, then post representative
schema and queries. Right now you're just throwing
On Fri, Oct 2, 2015 at 7:43 AM, Bart Smissaert
wrote:
> > The Uniqueness of the output depends on which fields are included, JOINs,
> UNIONs, etc. etc.
>
> I am not talking about that situation. I am only referring to a situation
> where you want to count all
> rows in a table. I know it will be
23 matches
Mail list logo
