Tons of data, complex calculations, busy forms, and even network traffic impact a database’s performance. Your first line of defense against poor performance is a good design that includes appropriate indexes. An index is an underlying structure that determines how Access stores and retrieves data. By default, Access applies an index as follows:

  • To a primary key field.
  • To a foreign key if the relationship in question enforces referential integrity.

Access allows only 32 indexes on a single table, which frankly, is more than adequate for most of us. That number includes the indexes that Access sets automatically to primary and foreign key fields (see above). Adding a second index won’t hurt anything, but both will count toward the 32 limit.

You must index non-primary key fields yourself, and that’s where some folks get into trouble: Users fail to apply them or users apply them incorrectly. The following guidelines should help you decide whether a specific index is appropriate:

  • Don’t index just because you can.
  • Don’t index tables with little data.
  • Don’t index just to sort data.
  • Don’t index just to catch duplicate values during data entry.
  • Index a field when the data is mostly unique—in other words, there are more different entries than duplicates.
  • Index a field if you plan to search or sort against the field frequently.

The key to efficient indexing is to remember that an index should improve performance. Don’t apply an index for any other reason than performance. There are better ways to sort and validate data.

On the down side, applying an index to improve performance in one area almost always slows things down in another. For instance, an index might speed up a sort or search, but it will almost always  slow down data entry. That’s because Access refers to the index and sorts the index accordingly every time you update the indexed field. Make sure the trade-off is a worthy one. Simply ask yourself whether users will sort and search more than they enter and edit data or vice versa.