Subject Re: [firebird-support] Indexes - Ques for both IB and FB
Author Ann W. Harrison
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

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?