At 04:53 PM 24/09/2003 +0000, you wrote:

>Hi Helen,
>
>Thanks for the reply! I guess Firebird's indexes work differently
>to SAP's!!
>
>Have done what you said - removed all indexes on metrics table, and
>then added single indexes on exectime and copies.
>
>But, I am puzzled by what I see...
>
>If I run the original query again, I get the same results:
>
> SELECT distinct kiosk_id FROM Metrics WHERE ExecTime IS NOT NULL
>AND Copies IS NOT NULL
>
>=> PLAN SORT ((METRICS NATURAL))
>
>If I try the following query, it does use the index:
>
> SELECT distinct kiosk_id FROM Metrics WHERE ExecTime
>= '09/11/2003 16:36:17:0000'
>
>=> PLAN SORT ((METRICS INDEX (M_EXECTIME)))
>
>However, if I then add a where clause on Copies, it doesn't seem to
>include that second index:
>
> SELECT distinct kiosk_id FROM Metrics WHERE ExecTime
>= '09/11/2003 16:36:17:0000' AND copies='1'
>
>=> PLAN SORT ((METRICS INDEX (M_EXECTIME)))
>
>So, some questions:
>
>- why does Firebird not use the index when comparing if NULL?

There is no value to search, it's a mere exclude/include (Null or not
null). There is no reason to use the index.

The index on copies may be too costly because there are only a few distinct
values spread across a large table; or a predominance of one particular
value. If that is the case, then you shouldn't index it at all.

>- when using 2 columns in the where clause and specifying values,
>does Firebird use the second individual index but not show it?

No, it shows all of the indexes it uses. The optimizer is cost-based, not
rule-based. So it doesn't use indexes that will not be faster than a
natural scan.

>- am I correct in my understanding that I only need to index on
>columns that are in the WHERE clause, not columns that are being
>selected?

Indexes speed up searches (WHERE, expressions), joins and ordering (ORDER
BY, GROUP BY).

Don't be surprised if Firebird doesn't behave like SAPDB. They are vastly
differently architecturally.

heLen