Re: [sqlite] "is not null" and index

Information Systems From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on behalf of Igor Tandetnik [itandet...@mvps.org] Sent: Monday, October 17, 2011 10:06 AM To: sqlite-users@sqlite.org Subject: EXT :Re: [sqlite] "is not null" and index On 10/17/2011 9:30 AM, Blac

Re: [sqlite] "is not null" and index

On 10/17/2011 9:30 AM, Black, Michael (IS) wrote: According to this benchmark the break-even point is at 40% nulls. I asssume you have a different test? I did mention "rule of thumb". Specific cases may vary. I must admit I'm too lazy to build tests for someone else's problem. The fact

Re: [sqlite] "is not null" and index

rop Grumman Information Systems From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on behalf of Igor Tandetnik [itandet...@mvps.org] Sent: Monday, October 17, 2011 8:14 AM To: sqlite-users@sqlite.org Subject: EXT :Re: [sqlite] "is not

Re: [sqlite] "is not null" and index

On Mon, Oct 17, 2011 at 9:14 AM, Igor Tandetnik wrote: > Jean-Christophe Deschamps wrote: > >>> How can indexes be used with "not null" queries? > >> > >> They cannot. > > > > If one sees NOT NULL as the complement of NULL, i.e. values in the > > range

Re: [sqlite] "is not null" and index

Jean-Christophe Deschamps wrote: >>> How can indexes be used with "not null" queries? >> >> They cannot. > > If one sees NOT NULL as the complement of NULL, i.e. values in the > range {min_value, max_value} (min and max depending on the column > expected content and type),

Re: [sqlite] "is not null" and index

Yoav Apter wrote: > I have the following table: > > CREATE TABLE x ('col1', 'col2', 'col3') > Create col1index on x ('col1') > > When I run this query: "select * from x where col1 is null" I see the index on x is used. > When I run this query: "select * from x where col1

Re: [sqlite] "is not null" and index

Black, Michael (IS) wrote: > What does distinct do? > sqlite> explain query plan select distinct (col1) from x where col1 is not > null; > sele order from deta > - > 0 0 0 SCAN TABLE x USING COVERING INDEX

Re: [sqlite] "is not null" and index

Unit Northrop Grumman Information Systems From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on behalf of Igor Tandetnik [itandet...@mvps.org] Sent: Monday, October 17, 2011 6:56 AM To: sqlite-users@sqlite.org Subject: EXT :Re: [sqlite] "is not null" and index

Re: [sqlite] "is not null" and index

Yoav Apter wrote: > I have the following table: > > CREATE TABLE x ('col1', 'col2', 'col3') > Create col1index on x ('col1') > > When I run this query: "select * from x where col1 is null" I see the index > on x is used. > When I run this query: "select * from x where

[sqlite] "is not null" and index

Hi I have the following table: CREATE TABLE x ('col1', 'col2', 'col3') Create col1index on x ('col1') When I run this query: "select * from x where col1 is null" I see the index on x is used. When I run this query: "select * from x where col1 is NOT null" I see the index on x is not used.