| Subject | Re: [firebird-support] Index not used by "where xxx not null" query? |
|---|---|
| Author | Ann W. Harrison |
| Post date | 2006-02-27T17:28:24Z |
Nando Dessena wrote:
beginning of (its) InterBase / Firebird have _not_ used
indexes with _NOT_ conditions. Whether (or _not_) that's
appropriate with expressions like NOT NULL is debatable.
These are indexable:
where x.a = 1
where x.a > 10
where x.a <= 10
where x.a IS NULL
where x.a between 1 and 10
These are _not_
where x.a NOT = 1
where x.a NOT > 10
where x.a NOT <= 10
where x.a IS NOT NULL
where x.a NOT between 1 and 10
Regards
Ann
>Err, _not_ quite. In fact the key is the "_not_". Since the
> KR> is null uses the
> KR> ascending index but is not null doesn't use neither
>
> I don't see anything particularly strange in your case. Equality tests
> use indexes, inequality tests don't. Why do you expect is null and is
> not null to behave differently?
>
beginning of (its) InterBase / Firebird have _not_ used
indexes with _NOT_ conditions. Whether (or _not_) that's
appropriate with expressions like NOT NULL is debatable.
These are indexable:
where x.a = 1
where x.a > 10
where x.a <= 10
where x.a IS NULL
where x.a between 1 and 10
These are _not_
where x.a NOT = 1
where x.a NOT > 10
where x.a NOT <= 10
where x.a IS NOT NULL
where x.a NOT between 1 and 10
Regards
Ann