Subject RE: [firebird-support] Question about Firebird indexes
Author Svein Erling Tysvær
Sorry, forgot to mention why I generally prefer single field indexes: It makes it easier for me to understand the PLAN that Firebird suggests. With a compound index, I wouldn't know whether Firebird used the index only for the first field in the index or if it also used it for other fields that were part of the index. Moreover, to me it feels like things have to be more thoroughly thought through when using combined indexes - you have to ascertain that all (important) cases are covered. With single field indexes, it is very simple to see whether a field is indexed or not, you don't have to reason like 'yes, if tableb.fieldb is specified in your JOIN clause then an index covering tableb.fielda may be used provided tableb is later than tablea in the plan'.


-----Original Message-----
From: [] On Behalf Of Svein Erling Tysvær
Sent: 1. juli 2008 10:16
To: ''
Subject: RE: [firebird-support] Question about Firebird indexes

Indexing the two columns within the same index, is a little bit faster when your search specifies both of them - if you use partial match like BETWEEN or STARTING, then it may also matter in which order you specify the fields within the index. When searching for only one of the fields, then this combined index can be used for the first of the fields in the index (in your case DOCUMENTTYPEID), but not for other fields (DOCUMENTTYPE).

I tend to mostly use single field indexes myself - most of my queries only require decent performance and I don't bother spend time trying to make things 10% or 20% faster (if users complain about things taking 10 seconds, they would hardly shout 'hooray' if you made it do the same in 8 seconds - at least not if a side effect could be that other things increased from 5 seconds to 5 minutes).

There is one further thing to notice, that non-selective indexes makes updates and deletes slower on older Firebird versions, I think it is better on Firebird 2.x, but don't know for certain since I'm still on Firebird 1.5 myself. The common solution to this problem, was to add the primary key or something to the end of the index (or you could the combined index you suggested if that makes things more selective, but that makes the optimizer think that the index is more selective than it actually is and may increase the odds of the optimizer choosing the wrong plan (Firebird 2.1 has separate statistics for each field in the index, Firebird 1.5 hasn't - I don't know about 2.0).


-----Original Message-----
From: [] On Behalf Of Zd
Sent: 1. juli 2008 09:51
Subject: [firebird-support] Question about Firebird indexes

Dear Group,

I know that you can put an index on a single column and on multiple columns at the same time. But you can also put a separate index on each column - my question is: what's the difference?




I simply want to index two columns of my table, so when I search SEPARATELY for either DocumentTypeID or DocumentType, I want the SELECT to be as fast as possible. Which is the road to take?

Thanks for the info: