Too many indexes?

[Leslie Isaacs]

Hello All

I am using A97.

I am carrying out various measures to improve the performance of my db, and
one of these is to create more indexes in some of the tables. I understand
(I think!) that it is important to index those fields that are used in
'GroupBy' or 'Sum' etc. expression in any queries, or that are in any
joins.

My question is this: is there any potential 'downside' to creating indexes?
If I indexed a field that didn't really need to be indexed, would that have
a negative effect on performance - or any other disadvantage?

Hope someone can help me understand this properly.

Many thanks
Leslie Isaacs

 

[Rick Brandt]

Hello All

I am using A97.

I am carrying out various measures to improve the performance of my
db, and one of these is to create more indexes in some of the tables.
I understand (I think!) that it is important to index those fields
that are used in 'GroupBy' or 'Sum' etc. expression in any queries,
or that are in any joins.

My question is this: is there any potential 'downside' to creating
indexes? If I indexed a field that didn't really need to be indexed,
would that have a negative effect on performance - or any other
disadvantage?

Hope someone can help me understand this properly.

Many thanks
Leslie Isaacs

Indexes make your file larger and can make updates slower (because the
indexes have to be modified as well). Other than that there is not much of
a downside.

 

[John Vinson]

Hello All

I am using A97.

I am carrying out various measures to improve the performance of my db, and
one of these is to create more indexes in some of the tables. I understand
(I think!) that it is important to index those fields that are used in
'GroupBy' or 'Sum' etc. expression in any queries, or that are in any
joins.

As a rule of thumb, you benefit from indexing any field that is used
in joining tables (essential), sorting (highly beneficial), or
searching (highly beneficial). It's not particularly any benefit to
summing (if the fields used for grouping are indexed).

My question is this: is there any potential 'downside' to creating indexes?
If I indexed a field that didn't really need to be indexed, would that have
a negative effect on performance - or any other disadvantage?

There are downsides: while having more indexes improves search and
retrieval performance, it *hurts* database update performance, since
Access must update all the indexes as well as updating the table. In
addition, the size of the indexes contributes to the overall size of
your database, using more disk, pushing the 2 GByte limit on database
size closer, and (often) causing additional performance hits if the
database file becomes fragmented. There is a limit of 32 indexes on
any one table (including automatically generated indexes created by
relationships or by <yuck!> lookup fields).

There's a bit of art as well as logic to creating just the right
indexing scheme for any given database; it is quite possible to
over-index as well as to under-index.

John W. Vinson[MVP]