If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
adding Indexes to fields in tables
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 |
#2
|
|||
|
|||
On Wed, 8 Sep 2004 14:10:17 -0700, "Paul James"
wrote: 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) |
#3
|
|||
|
|||
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. |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Trouble with updating fields in a form from two tables | Julie Nicole | New Users | 0 | August 5th, 2004 10:41 AM |
Adding auto-numbered field screws up Table order | Carl | Database Design | 5 | May 30th, 2004 03:25 AM |
Adding New Fields to an existing form | Sarah Hill | Database Design | 3 | May 28th, 2004 04:54 PM |
linking tables with unmatched fields | tachia | Database Design | 1 | April 30th, 2004 03:53 PM |