Re: [sqlite] Fwd: SELECT Max(IndexedField) doesn't use partial index

[jose isaias cabrera]

Scott Robison wrote...


Yes, it might just need to walk through a larger data set. If nulls are
rare, there probably wouldn't be much of a difference (not in front of a
computer to check). If the column was mostly nulls, and a lot of rows, it
might be a lot faster.


Hmmm... Thanks.  I have lots of INDEXes to re-INDEX.  Thanks...

josé 


___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Fwd: SELECT Max(IndexedField) doesn't use partial index

[Scott Robison]

On May 9, 2017 9:07 PM, "jose isaias cabrera"  wrote:


Scott Robison wrote...

On Mon, May 8, 2017 at 11:40 AM, Paul van Helden 
wrote:

> Hi,
>>
>> I use a lot of indexes on fields that typically contain lots of NULLs, so
>> the WHERE NOT NULL partial indexing seems very useful.
>>
>> However when I compare the "EXPLAIN QUERY PLAN" with a partial index vs. a
>> normal index, SQLite won't use the index to find Max(IndexedField) if it
>> is
>> a partial index.
>>
>> Is this an optimization opportunity? I understand that other kinds of
>> partial indexes might exclude possible Min or Max values, but a NOT NULL
>> index would be fine for mins, maxes and most other things?
>>
>
This may be an optimization opportunity, but you can easily force the
> use of that index by stating WHERE NOT NULL in the select query
> itself. For example:
>
> CREATE TABLE a(b);
> CREATE INDEX ab on a(b) where b is not null;
>
> sqlite> explain query plan select max(b) from a;
> 0|0|0|SEARCH TABLE a
> sqlite> explain query plan select max(b) from a where b is not null;
> 0|0|0|SEARCH TABLE a USING COVERING INDEX ab
>

So, a newbie question here...  What happens if you have,

INDEX ab on a(b);

and no "where b is not null".  Doesn't the INDEX takes care of all those
nulls and the same outcome would result?

thanks.


Yes, it might just need to walk through a larger data set. If nulls are
rare, there probably wouldn't be much of a difference (not in front of a
computer to check). If the column was mostly nulls, and a lot of rows, it
might be a lot faster.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Fwd: SELECT Max(IndexedField) doesn't use partial index

[jose isaias cabrera]

Scott Robison wrote...
On Mon, May 8, 2017 at 11:40 AM, Paul van Helden  
wrote:

Hi,

I use a lot of indexes on fields that typically contain lots of NULLs, so
the WHERE NOT NULL partial indexing seems very useful.

However when I compare the "EXPLAIN QUERY PLAN" with a partial index vs. 
a
normal index, SQLite won't use the index to find Max(IndexedField) if it 
is

a partial index.

Is this an optimization opportunity? I understand that other kinds of
partial indexes might exclude possible Min or Max values, but a NOT NULL
index would be fine for mins, maxes and most other things?



This may be an optimization opportunity, but you can easily force the
use of that index by stating WHERE NOT NULL in the select query
itself. For example:

CREATE TABLE a(b);
CREATE INDEX ab on a(b) where b is not null;

sqlite> explain query plan select max(b) from a;
0|0|0|SEARCH TABLE a
sqlite> explain query plan select max(b) from a where b is not null;
0|0|0|SEARCH TABLE a USING COVERING INDEX ab


So, a newbie question here...  What happens if you have,

INDEX ab on a(b);

and no "where b is not null".  Doesn't the INDEX takes care of all those 
nulls and the same outcome would result?


thanks.


___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Fwd: SELECT Max(IndexedField) doesn't use partial index

[Paul van Helden]

On Mon, May 8, 2017 at 8:20 PM, Scott Robison 
wrote:

> On Mon, May 8, 2017 at 11:40 AM, Paul van Helden 
> wrote:
> > Hi,
> >
> > I use a lot of indexes on fields that typically contain lots of NULLs, so
> > the WHERE NOT NULL partial indexing seems very useful.
> >
> > However when I compare the "EXPLAIN QUERY PLAN" with a partial index vs.
> a
> > normal index, SQLite won't use the index to find Max(IndexedField) if it
> is
> > a partial index.
> >
> > Is this an optimization opportunity? I understand that other kinds of
> > partial indexes might exclude possible Min or Max values, but a NOT NULL
> > index would be fine for mins, maxes and most other things?
>
> This may be an optimization opportunity, but you can easily force the
> use of that index by stating WHERE NOT NULL in the select query
> itself. For example:
>
> CREATE TABLE a(b);
> CREATE INDEX ab on a(b) where b is not null;
>
> sqlite> explain query plan select max(b) from a;
> 0|0|0|SEARCH TABLE a
> sqlite> explain query plan select max(b) from a where b is not null;
> 0|0|0|SEARCH TABLE a USING COVERING INDEX ab
>
> >


Thanks Scott,

That works and fully solves my problem!

Regards,

Paul.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Fwd: SELECT Max(IndexedField) doesn't use partial index

[Scott Robison]

On Mon, May 8, 2017 at 11:40 AM, Paul van Helden  wrote:
> Hi,
>
> I use a lot of indexes on fields that typically contain lots of NULLs, so
> the WHERE NOT NULL partial indexing seems very useful.
>
> However when I compare the "EXPLAIN QUERY PLAN" with a partial index vs. a
> normal index, SQLite won't use the index to find Max(IndexedField) if it is
> a partial index.
>
> Is this an optimization opportunity? I understand that other kinds of
> partial indexes might exclude possible Min or Max values, but a NOT NULL
> index would be fine for mins, maxes and most other things?

This may be an optimization opportunity, but you can easily force the
use of that index by stating WHERE NOT NULL in the select query
itself. For example:

CREATE TABLE a(b);
CREATE INDEX ab on a(b) where b is not null;

sqlite> explain query plan select max(b) from a;
0|0|0|SEARCH TABLE a
sqlite> explain query plan select max(b) from a where b is not null;
0|0|0|SEARCH TABLE a USING COVERING INDEX ab

>
> Regards,
>
> Paul.
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users



-- 
Scott Robison
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users