adding Indexes to fields in tables

[Paul James]

I've been told that you can improve the performance of a Jet database if you
index all fields used as criteria fields in queries.

Of course I'd always like to speed up my databases, but I'd also like to
know if there's any drawback to creating lots of indexes in the database.
That is, does it have any disadvantage, such as making it more prone to
corruption, or anything else that could have a negative effect?

It must add some kind of overhead, otherwise Access would have been designed
to build an index for every table field. So at what point does it become
undesireable to create additional indexes for query criteria fields?

Thanks in advance,

Paul

 

[John Vinson]

I've been told that you can improve the performance of a Jet database if you
index all fields used as criteria fields in queries.

Of course I'd always like to speed up my databases, but I'd also like to
know if there's any drawback to creating lots of indexes in the database.

There are three drawbacks:

- You are limited to a total of 32 indexes on any one table (including
the Primary Key, any indexes created by Relationships, and the indexes
created by Lookup Fields)
- You will gain on search performance, but lose performance on
updating the table (since all the indexes must be updated)
- The database will bloat faster since each index takes up space

It must add some kind of overhead, otherwise Access would have been designed
to build an index for every table field. So at what point does it become
undesireable to create additional indexes for query criteria fields?

That's one reason that having a good DBA is worth the expense. There's
a lot of art, and of experimentation, involved in getting a good
answer to that question for any particular database!

John W. Vinson[MVP]
(no longer chatting for now)

 

[Paul James]

Thanks for the information, John.

I'm not even close to the 32 field limit, so I probably won't have any
problem indexing my query criteria fields.