I was reading this article from Ann Harrison

http://www.ibphoenix.com/main.nfs?page=ibp_expert1

This particular section is interesting
Index optimization

Because their access strategy binds index access and record access
tightly, most database optimizers must choose one index per table as
the path to data. Firebird can use several indexes on a table by
�AND�ing and �OR�ing the bitmaps it creates from the index before
accessing any data.

If you have a table where several different fields are used to restrict
the data retrieved from a query, most databases require that you define
a single index that includes all the fields. For example, if you are
looking for a movie that was released in 1964, directed by Stanley
Kubrick, and distributed by Columbia you would need an index on Year,
Director, and Distributor. If you ever wanted to find all pictures
distributed by Stanley Kubrick, you would also need an index on
Director alone etc. With Firebird, you would define one index on
Director, one on Distributor, and one on ReleaseDate and they would be
used in various combinations.

So I wonder how important is to have composite index with multiple columns? Is it even necessary in Firebird?

--- On Tue, 7/1/08, Svein Erling Tysvær <svein.erling.tysvaer@...> wrote:
From: Svein Erling Tysvær <svein.erling.tysvaer@...>
Subject: RE: [firebird-support] Question about Firebird indexes
To: "'firebird-support@yahoogroups.com'" <firebird-support@yahoogroups.com>
Date: Tuesday, July 1, 2008, 3:33 AM











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



Set



-----Original Message-----

From: firebird-support@ yahoogroups. com [mailto:firebird-support@ yahoogroups. com] On Behalf Of Svein Erling Tysvær

Sent: 1. juli 2008 10:16

To: 'firebird-support@ yahoogroups. com'

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



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_DEFAULTDOCUMENT S ON DEFAULTDOCUMENTS( DOCUMENTTYPEID, DOCUMENTTYPE) ;



or



CREATE INDEX IDX_DEFAULTDOCUMENT S1 ON DEFAULTDOCUMENTS( DOCUMENTTYPEID) ;

CREATE INDEX IDX_DEFAULTDOCUMENT S2 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



























[Non-text portions of this message have been removed]