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).

HTH,
Set

-----Original Message-----
From: firebird-support@yahoogroups.com [mailto:firebird-support@yahoogroups.com] On Behalf Of Zd
Sent: 1. juli 2008 09:51
To: firebird-support@yahoogroups.com
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?

Eg:
CREATE INDEX IDX_DEFAULTDOCUMENTS ON DEFAULTDOCUMENTS(DOCUMENTTYPEID,DOCUMENTTYPE);

or

CREATE INDEX IDX_DEFAULTDOCUMENTS1 ON DEFAULTDOCUMENTS(DOCUMENTTYPEID);
CREATE INDEX IDX_DEFAULTDOCUMENTS2 ON DEFAULTDOCUMENTS(DOCUMENTTYPE);


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:
Zd