Thomas Miller wrote:

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

Err, yes, but it's a big topic.

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

Generally single field indexes are preferred. If you have a column
with two conditions and select on both, Firebird and InterBase will
use both indexes. Most databases have to do their lookups on a single
index.

However, if you have a field with very few distinct values (e.g. Gender)
the index created will not be very good unless it is combined with some
more specific value (e.g. Gender + EmployeeNumber)

>
> Will the DB use the first column on a complex column index if it
> matches, or do I need a separate index?

Yes. If you you supply the first two columns in a four column
index, both will be used.

>
> INDEX_1(columnA, columnB, columnC)
>
> select * from MYTABLE
> order by columnA
>
> Will this use the index?

Maybe, bit it probably shouldn't. Indexes are for selecting a subset
of the records in a table, not for ordering records. It is measurably
faster to read all records in their natural order and sort them than to
read them in index order.

How about joining tables?

Yes.

Regards,


Ann