Create and use an index to improve performance

Applies to: Access for Office 365, Access 2019, Access 2016, Access 2013, Access 2010, Access 2007.

If you usually search a table in Access or sort its records by a particular field, you can quicken these operations by setting an index for the field. Access uses indexes in a table as you use an index in a book: to obtain data, Access scours the location of the data in the index. In some instances, like for a primary key, Access instantly assigns an index for you. For other cases, you may prefer to make an index yourself.

This article introduces indexes and covers how to decide which fields to index, and how to create, delete, or change an index. It also explains the conditions under which Access automatically creates indexes.

In this article

What is an index?

Decide which fields to index

Create an index

Delete an index

View and edit indexes

Automatic index creation

Note: You cannot use the methods described by this article to create an index for a table in a web database. Performance of a web database depends on the performance of several factors such as the SharePoint server that is hosting the web database.

What is an index?

You can use an index to aid Access in discovering and sorting records quicker. An index preserves the location of records according to the field or fields that you select to index. After Access retrieves the location from the index, it can then acquire the data by jumping straight to the correct location. In this way, using an index can be substantially faster than scanning through all of the records to find the data.

Decide which fields to index

You can design indexes that are linked to a single field or on multiple fields. You’ll ideally want to index fields that you search often, fields that you sort, and fields that you join to fields in other tables in several table queries. Indexes can hasten searches and queries, but they can stifle performance once you create or amend data. After you enter data in a table that includes one or more indexed fields, Access must revise the indexes constantly after a record is added or changed. Inserting records by using an append query or by appending imported records is also probable to be slower if the destination table has indexes.

Note: The primary key of a table is automatically indexed.

You cannot index a field whose data type is OLE Object, Calculated, or Attachment. For other fields, consider indexing a field if all of the following apply:

  • The field’s data type is Short Text (Text in Access 2010), Long Text (Memo in Access 2010), Number, Date/Time, AutoNumber, Currency, Yes/No or Hyperlink.
  • You anticipate searching for values stored in the field.
  • You anticipate sorting values in the field.
  • You anticipate storing many different values in the field. If many of the values in the field are the same, the index might not significantly speed up queries.

Multiple-field indexes

If you think that you’ll frequently search or sort by two or more fields at once, you can produce an index for that combination of fields. For example, if you often set criteria for the Vendor and ProductName fields in the same query, it makes sense to create a multiple-field index on both fields.

Once you sort a table by a multiple-field index, Access sorts first by the starting field defined for the index. You define the order of the fields once you format a multiple-field index. If there are records with duplicate values in the first field, Access sorts next by the second field specified for the index, and so on.

You can add up to 10 fields in a multiple-field index.

Create an index

To make an index, you first deliberate whether you want to produce a single-field index or a multiple-field index. You design an index on a single field by setting the Indexed property. The following table lists the possible settings for the Indexed property.

Indexed property settingMeaning
NoAvoid creating an index on this field (or delete the existing index).
Yes (Duplicates OK)Create an index on this field.
Yes (No Duplicates)Create a unique index on this field.

If you produce a unique index, Access prevents you from typing a new value in the field if that value already exists in the same field in another record. Access promptly creates a unique index for primary keys, but you may even want to forbid duplicate values in other fields. For example, you can create a unique index on a field that stores serial numbers so that no two products have the same serial number.

Create a single-field index    

  1. In the Navigation Pane, right-click the name of the table that you want to form the index in, and then pick Design View on the shortcut menu.

  1. Choose the Field Name for the field that you want to index.

  1. Under Field Properties, select the General tab.

  1. In the Indexed property, press Yes (Duplicates OK) if you want to enable duplicates, or Yes (No Duplicates) to produce a unique index.

  1. To save your changes, press Save on the Quick Access Toolbar, or press CTRL+S.

Create a multiple-field index    

To design a multiple-field index for a table, you contain a row for each field in the index and embody the index name merely in the first row. Access judges each row as part of the same index until it reaches a row encompassing another index name. To add a row, right-click your desired location to add a row, and then select Insert Rows on the shortcut menu.

  1. In the Navigation Pane, right-click your particular table’s name in which you want to set the index in, and then press Design View on the shortcut menu.

  1. On the Design tab, in the Show/Hide group, pick Indexes. The Indexes window displays. Resize the window to ensure that some blank rows emerge and the index properties are presented.

  1. In the Index Name column, in the first blank row, enter a name for the index. You can label the index after one of the index fields, or use another name.

  1. In the Field Name column, select the arrow and then choose the first field that you want to use for the index.

  1. In the next row, leave the Index Name column blank, and then, in the Field Name column, press the second field for the index. Repeat this step until you pick each required field that you want to add in the index.

  1. To adjust the sort order of the field’s values, in the Sort Order column of the Indexes window, pick Ascending or Descending. The standard sort order is Ascending.

  1. In the Indexes window, under Index Properties, format the index properties for the row in the Index Name column that has the index’s name. Set the properties in alignment with the following table.
PrimaryIf Yes, the index becomes the primary key.
UniqueIf Yes, each value in the index should be unique.
Ignore NullsIf Yes, records with a Null value in the indexed fields are discounted from the index.

  1. To save your changes, press Save on the Quick Access Toolbar or press CTRL + S.

  1. Quit the Indexes window.

Delete an index

If you learn that an index becomes obsolete or is having a profound impact on performance, you can erase it. Once you delete an index, you delete merely the index and not the field or fields on which it is developed.

  1. In the Navigation Pane, right-click your desired table’s name to erase the index in, and then pick Design View on the shortcut menu.
  1. On the Design tab, in the Show/Hide group, select Indexes.The Indexes window appears. Resize the window so that some blank rows appear and the index properties are shown.
  1. In the Indexes window, select the row or rows that contain the index that you want to delete, and then press DELETE.
  1. To save your changes, click Save on the Quick Access Toolbar or press CTRL + S.

  1. Close the Indexes window.

View and edit indexes

You might want to see the indexes for a table to weigh their impact on performance, or to ensure that particular fields are indexed.

  1. In the Navigation Pane, right-click the name of the table that you want to edit the index in, and then click Design View on the shortcut menu.
  1. On the Design tab, in the Show/Hide group, click Indexes. The Indexes window launches. Resize the window so that some blank rows appear and the index properties are shown.
  1. Check or edit the indexes and index properties to match your needs.
  1. To save your changes, press Save on the Quick Access Toolbar or press CTRL + S.
  1. Exit the Indexes window.

Automatic index creation

In some instances, Access intuitively forms indexes for you. For example, an index is automatically created for any field or fields that you designate as a table’s primary key.

Another source of automatic index creation is the AutoIndex on Import/Create option in the Access Options dialogue box. Access instantly indexes any fields with names that begin or end with the characters typed in the AutoIndex on Import/Create box, such as IDkeycode, or num. To view or alter the current setting, take the following steps:

  1. Select File > Options
  1. Pick Object Designers and then, below Table design, add, edit, or delete values in the AutoIndex on Import/Create box. Use a semicolon (;) to separate values.

Note: If a field name begins or ends with a value listed in the box, the field is automatically indexed.

  1. Press OK.

Because each extra index needs Access to do additional work, performance declines with creating or updating data. You might, therefore, want to contemplate carefully about editing the values shown in the AutoIndex on Import/Create box or reducing the number of values to minimise the number of indexes created.

Top of Page

Leave a Reply

%d bloggers like this: