Zd wrote:

>
> 3, Let's say I create an index on multiple columns:
> CREATE INDEX IDX_MYTABLE ON MYTABLE(COL1,COL2);

In Firebird it's generally better to create two indexes,
one for each column. If the selection is based on two
indexed columns, Firebird will use both indexes.

>
> Now if I call
> SELECT somestuff FROM MyTable WHERE Col1 >= 0 AND Col2 = 1 ORDER BY Col1;
> I suppose FB will use the index.

Indexes exist in Firebird for locating records, not for eliminating
a sort. Sorts are cheap. Random disk access is not. Firebird uses
the index to read records efficiently.

Second point, in a compound index Firebird, like most other systems
that use B*tree indexes, can use the second term of a compound index
only if the condition on the first term is equality. Same thing for
the third term - it's used only if the first two are equality.

However, if you had separate indexes on Col1 and Col2, Firebird
would use both, even if neither was an equality condition.

>
> But if I call
> SELECT somestuff FROM MyTable WHERE Col1 >= 0
> will FB use my index, or do I have to create a separate index only for Col1?

Firebird will use the index, probably, but not in the future if
the optimizer learns to keep range information about indexes and
determines that all values of Col1 are >= 0.

>

Best regards,


Ann