Subject | Question about indexes |
---|---|
Author | Russell Eva |
Post date | 2004-03-08T14:13:43Z |
I keep on having this argument with everyone and it seems there are as many
documented proofs as there are people.
On a table with many columns what is recommended (in Firebird)
Many simple indexes (ie one field per index) to allow the RDBMS to work it
out for itself
Or
Few complex indexes based on the majority of the querys fired against the
table, taking into consideration that a query where clause interrogating
columns a, b, and c may use an index comprising a, b, c, and d.
All documentation that I have read advise against indexes on columns that
have minimal values (eg active_flag (y/n) etc), and in my experience the
more indexes on a table, the worse the performance of inserts etc. I do
realize that this varies from DB to DB, but at this stage I am only
interested in FB.
My preference is to have a simple pk (ie a generator) (this also keeps fk's
simple) and then
Complex indexes based on table use.
What is the general feeling in this respect?
Tks
Russell
documented proofs as there are people.
On a table with many columns what is recommended (in Firebird)
Many simple indexes (ie one field per index) to allow the RDBMS to work it
out for itself
Or
Few complex indexes based on the majority of the querys fired against the
table, taking into consideration that a query where clause interrogating
columns a, b, and c may use an index comprising a, b, c, and d.
All documentation that I have read advise against indexes on columns that
have minimal values (eg active_flag (y/n) etc), and in my experience the
more indexes on a table, the worse the performance of inserts etc. I do
realize that this varies from DB to DB, but at this stage I am only
interested in FB.
My preference is to have a simple pk (ie a generator) (this also keeps fk's
simple) and then
Complex indexes based on table use.
What is the general feeling in this respect?
Tks
Russell