> Anyone have some good general info on how both or either uses indexes
> for optimization?

There is a fair bit out there. A lot of the IB documentation about it
is 99% applicable for FB, so also google for some of the white papers
written by Craig Stuntz.

Ann has written a paper on Indices too:
http://www.ibphoenix.com/main.nfs?a=ibphoenix&page=ibp_expert1

Some admin tools provide a plan analyiser the will show you which
indices are used for a given query, which can help you to fine tune.

>
> Things specifically I have questions about
>
> Is it faster to have complex (multi-column) indexes then multiple

single

> column indexes?

If your where clause contains each field of a multi-column index, this
will be faster. But FB (and IB) are able to make use of multiple
single column indices where they are available.

>
> Will the DB use the first column on a complex column index if it
> matches, or do I need a separate index?
>
> INDEX_1(columnA, columnB, columnC)

The problem with a multi-column index is that you must use each field
to the left of a field of interest. In your example above, you could
not use this index for a WHERE COLUMNB = ? unless you also have AND
Column A = ? in your where clause. You don't need to use column C.

>
> select * from MYTABLE
> order by columnA
>
> Will this use the index? How about joining tables? Thanks.

Yes, but it is no more beneficial than if you just had column A
indexed. Depending on the where clause and other joined tables, it may
not even use any index for the sorting if it feels there is a cheaper
method.

Firebird will always attempt to go the way it perceives to be the
least cost. Sometimes, it gets it wrong, and you need to prevent an
index being used that it not helpful. An example of an unhelpful index
is one with poor selectivity (eg on a gender table and yes there are
exceptions but lets assume a 50:50 ratio). With such an index, it
would be more expensive to check the index than to just do a manual
read of the data pages.

Also, pre-FB2 index structures means that indices with poor
selectivity are expensive to clean garbage out of. (Will be resolved
in FB2)

Make sure the statistics are up to date. Out of date statistics can
allow the optimiser to guess wrong about the cost of a path. (See SET
STATISTICS command). Garbage in .....

Avoid specifying a PLAN which disables the optimiser. There are better
ways to preventing particular indices from being used if they are a
problem.

Adam