Hello

Dne 07.04.2010 10:38, Zd napsal(a):

>
> Hello all,
>
> I'm really confused about Firebird and indices, I'd appreciate it if
> someone could shed some light on these issues: (I'm using FB 2.1)
>
> 1, The primary key is always indexed, right?
>

Yeah

>
>
> 2, If I create a new index on a table that has a large amount of data,
> will Firebird index the contents immediately, and begin using the
> index as soon as it's finished, or will I have to issue any command
> (other than Create Index) to make the index live?
>

If you create that index as active (it's default behavior), the index is
created once you commit the transaction in which you ran that create
index command.

>
>
> 3, Let's say I create an index on multiple columns:
> CREATE INDEX IDX_MYTABLE ON MYTABLE(COL1,COL2);
>
> Now if I call
> SELECT somestuff FROM MyTable WHERE Col1 >= 0 AND Col2 = 1 ORDER BY Col1;
> I suppose FB will use the index.
>

It will use that index for order - not sure, if it will use index for
where condition because of >= operator

>
>
> 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?
>

Nope. FB will use that index for COL1 and COL2 (FB is able to use only a
part of an index)

>
>
> 4, What are SET STATISTICS INDEX IDX_MYTABLE for?
>

To recompute statistics on an index which helps optimalizer to choose
the right index for your query in some cases.

Milan



[Non-text portions of this message have been removed]