Even with large tables, however, indexing
can slow performance if the records in tables will be changed often or new records will be added
frequently. Each time a record is changed or added, Access must update all indexes in the table.
Given all the advantages of indexes, why not index everything in the table? What are the drawbacks
of indexing too many fields? Is it possible to over-index tables?
First of all, indexes increase the size of the .mdb file somewhat. Unnecessarily indexing a table that
doesn??™t really require an index eats up a bit of disk space for each record in the table. More important,
indexes extract a performance hit for each index on the table every time a record is added to
the table. Because Access automatically updates indexes each time a record is added (or removed),
the internal indexing must be adjusted for each new record. If you have ten indexes on a table,
Access makes ten adjustments to the indexes each time a new record is added or an existing record
is deleted, causing a noticeable delay on large tables (particularly on slow computers).
Pages:
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324