Keys in a table: Is there a point when all columns are important?

[Randell D.]

Folks,

I have a table that has five columns in it - It collates references to
records in five other tables. I understand that an index/key can help
improve performance when searching - however I also believe too many
keys can hamper performance since table updates also mean updateing the
indexes.

Thus - Since all five columns in my table can be used individually for
specific searches, would I be better off not having keys - Since the
keys/indexes would not have to be updated, I stand a performance
improvement in this situation,true?

Comments via the newsgroup are greatly appreciated, thanks,

Randell D.

[Bill Karwin]

Re: Keys in a table: Is there a point when all columns are important?

Randell D. wrote:
[color=blue]
> Thus - Since all five columns in my table can be used individually for
> specific searches, would I be better off not having keys - Since the
> keys/indexes would not have to be updated, I stand a performance
> improvement in this situation,true?[/color]

It's often the case that an application reads the data more than it
updates data. So the cost of updating the keys will likely give you
performance benefit on read operations many times. And depending on the
size of your dataset, the difference between an indexed search and a
non-indexed search can be huge. If the keys are actually going to get
used, it's almost certainly a net benefit to keep them.

I recommend that indexes should be removed when they are on columns that
are *never* used in search criteria, sorting, or join conditions. Even
if a column is only used very seldom, keeping an index maintained can be
justified if it's important that those rare searches run as quickly as
possible.

Regards,
Bill K.

[Randell D.]

Re: Keys in a table: Is there a point when all columns are important?

Bill Karwin wrote:[color=blue]
> Randell D. wrote:
>[color=green]
>> Thus - Since all five columns in my table can be used individually for
>> specific searches, would I be better off not having keys - Since the
>> keys/indexes would not have to be updated, I stand a performance
>> improvement in this situation,true?[/color]
>
>
> It's often the case that an application reads the data more than it
> updates data. So the cost of updating the keys will likely give you
> performance benefit on read operations many times. And depending on the
> size of your dataset, the difference between an indexed search and a
> non-indexed search can be huge. If the keys are actually going to get
> used, it's almost certainly a net benefit to keep them.
>
> I recommend that indexes should be removed when they are on columns that
> are *never* used in search criteria, sorting, or join conditions. Even
> if a column is only used very seldom, keeping an index maintained can be
> justified if it's important that those rare searches run as quickly as
> possible.
>
> Regards,
> Bill K.[/color]

Thanks for that...