Too many indexes?

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Leslie Isaacs - 19 Apr 2005 16:18 GMT

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

Reply to this Message

Rick Brandt - 19 Apr 2005 18:27 GMT

> Hello All
>
[quoted text clipped - 15 lines]
> 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.

Signature

I don't check the Email account attached
to this message. Send instead to...
RBrandt at Hunter dot com

Reply to this Message

John Vinson - 19 Apr 2005 19:30 GMT

>Hello All
>
[quoted text clipped - 5 lines]
>'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]

Reply to this Message