Subject Re: [firebird-support] Doubt about indexes
Author Ann W. Harrison
Nicolas Rodriguez wrote:
> Hi all.
> I've two tables: A (fields: f1, f2, f3, f4, a1, a2) and B (fields: f1, f2,
> f3, f4, b1, b2, b3). Table A has 15000 records and table B 315000 records. I
> generally perform joins between this to tables using a.f1=b.f1 and a.f2=b.f2and
> a.f3=b.f3 and a.f4 = b.f4. Both tables has an Id field as their primary
> keys. Both tables have an index with this four fields. Both tables have
> separated indexes like an index in f2 and f3 fields, an index in f4 field,
> etc, because they're used in WHERE clauses. Questions:
> 1) If I have a compound index with this four fields, if I do a select with a
> where clause like f1=<some value>, can FB use this index?


> 2) Could this duplicated indexes hurt database performance?

> 3) Any pointers on compound indexes' use?

Firebird can use more than one index to support a single query,
so compound indexes are not necessary unless they support a unique
constraint. In the case you describe, I would define an index
for each field.