Create and use an index to improve performance

If you often search a table or sort its records by a particular field, you can speed up these operations by creating an index for the field. Microsoft Office Access uses indexes in a table as you use an index in a book: to find data, Access looks up the location of the data in the index. In some instances, such as for a primary key, Access automatically creates an index for you. At other times, you might want to create 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.

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 the SharePoint server that is hosting the web database.

Note: The functionality explained in this article does not apply to “Access apps.” For more information about working with Access apps, please see <insert link to placeholder topic>

.

In this article

Overview

Decide which fields to index

Create an index

Delete an index

View and edit indexes

Automatic index creation

Overview

You can use an index to help Access find and sort records faster. An index stores the location of records based on the field or fields that are part of the index. Depending on the number of unique values of the indexed fields, an index can be much smaller than the table it describes, making it more efficient for Access to read.

Indexes work like book indexes

Consider a book index: it is a list of words that appear in the book. Each entry in the list also shows the page numbers for the pages that contain the word. If you want to find a particular word in a book, an index makes it much faster than thumbing through the book.

In Access, an index is like a list of field values that appear in a table. Each entry in the list also shows the locations for the records that contain the field values. If you want to find a particular field value, an index makes it much faster than reading through the whole table.

Indexes make finding rare field values faster

The larger the number of different field values in a table, the more an index helps improve search and select query performance. The more frequently a given field value appears in a table, the less improvement an index will provide. Think about words in a book: the word "the" probably appears so frequently that reading the list item for that word would take almost as long as reading the whole book! Similarly, a field that has only a few values does not benefit as much from being indexed, because each value appears so many times.

After Access obtains the location of the records from the index, it can retrieve the data from those records by moving directly to the correct location. In this manner, using an index can be much faster than scanning through all the records in the table to find the data.

Indexes make changing your data slower

When you append, delete, or update data, any indexes on the affected tables must be updated to reflect the changes. This can impede performance. Again, think of a book: if you make changes, the index must also change or it will be inaccurate. Changing the index takes time, and that is time that you would not spend if there were no index. Access works the same way: indexes make changing your data take longer.

Top of Page

Decide which fields to index

You can create an index that is based on a single field or on multiple fields. You should consider indexing fields that you search frequently, fields that you sort, and fields that you join to fields in other tables in queries. Indexes can help speed up searches and select queries, but they can slow down performance when you add or update data.

When you enter data in a table that contains one or more indexed fields, Access must update the indexes every time a record is added or changed. Adding records by using an append query or by appending imported records is also likely to be slower if the destination table contains indexes.

Note: The primary key of a table is automatically indexed. For more information about primary keys, see the See Also section.

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

  • 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 will often search or sort by two or more fields at a time, you can create an index for that combination of fields. For example, if you often specify criteria for the Vendor and ProductName fields in the same query, it makes sense to create a multiple-field index on both fields.

When you sort a table by a multiple-field index, Access sorts first by the first field defined for the index. You specify the order of the fields when you create a multiple-field index. If there are records with duplicate values in the first field, Access sorts next by the second field defined for the index, and so on.

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

Top of Page

Create an index

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

Indexed property setting

Meaning

No

Don't create 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 create a unique index, Access doesn't let you enter a new value in the field if that value already is in the same field in another record. Access automatically creates a unique index for primary keys, but you might also want to prevent 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.

For more information about primary keys, click the links in the See Also section.

Create a single-field index    

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

  2. Click the Field Name for the field that you want to index.

  3. Under Field Properties, click the General tab.

  4. In the Indexed property, click Yes (Duplicates OK) if you want to enable duplicates, or Yes (No Duplicates) to create a unique index.

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

Create a multiple-field index    

  1. In the Navigation Pane, right-click the name of the table that you want to create the index in, and then click Design View.

  2. On the Design tab, in the Show/Hide group, click Indexes.

    The Indexes window appears. Resize the window so that some blank rows appear and the index properties are shown.

    To create a multiple-field index for a table, you include a row in the Indexes window for each field in the index and include the index name only in the first row. Access treats all rows as part of the same index until it comes to a row that contains another index name. To insert a row, right-click the location where you want to insert a row, and then click Insert Rows on the shortcut menu.

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

  4. In the Field Name column, click the arrow and then click the first field that you want to use for the index.

  5. In the next row, leave the Index Name column blank, and then, in the Field Name column, click the second field for the index. Repeat this step until you select all the fields that you want to include in the index.

    Note: The default sort order is Ascending

  6. To change the sort order of the field's values, in the Sort Order column of the Indexes window, click Ascending or Descending.

  7. In the Indexes window, under Index Properties, specify the index properties for the row in the Index Name column that contains the name of the index. Set the properties according to the following table.

Label

Value

Primary

If Yes, the index is the primary key.

Unique

If Yes, every value in the index must be unique.

Ignore Nulls

If Yes, records with a Null value in the indexed fields are excluded from the index.

  1. To save your changes, click Save on the Quick Access Toolbar.

    Keyboard shortcut  Press CTRL+S.

  2. Close the Indexes window.

Top of Page

Delete an index

If you find that an index becomes unnecessary or has too great an effect on performance, you can delete it. When you delete an index, you remove only the index and not the field or fields on which it is built.

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

  2. On the Design tab, in the Show/Hide group, click Indexes.

    The Indexes window appears. Resize the window so that some blank rows appear and the index properties are shown.

  3. In the Indexes window, select the row or rows that contain the index that you want to delete, and then press DELETE.

    Tip: Make sure that you select the whole row.

  4. To save your changes, click Save on the Quick Access Toolbar.

    Keyboard shortcut  Press CTRL+S.

  5. Close the Indexes window.

Top of Page

View and edit indexes

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

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

  2. On the Design tab, in the Show/Hide group, click Indexes.

    The Indexes window appears. Resize the window so that some blank rows appear and the index properties are shown.

  3. View or edit the indexes and index properties to suit your needs.

  4. To save your changes, click Save on the Quick Access Toolbar.

    Keyboard shortcut  Press CTRL+S.

  5. Close the Indexes window.

Top of Page

Automatic index creation

In some instances, Access automatically creates 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 dialog box. Access automatically indexes any fields that have names that begin or end with the characters entered in the AutoIndex on Import/Create box, such as ID, key, code, or num. To see or change the current setting, take the following steps:

  1. On the File tab, click Options.

  2. In the left pane of the Access Options dialog box, click Object Designers and then, under Table design view, edit the values in the AutoIndex on Import/Create box. Use a semicolon (;) to separate values.

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

  3. Click OK.

Because each additional index requires Access to do additional work, performance decreases when adding or updating data. You might, therefore, want to consider changing the values shown in the AutoIndex on Import/Create box or reducing the number of values to minimize the number of indexes created.

Top of Page

Share Facebook Facebook Twitter Twitter Email Email

Was this information helpful?

Great! Any other feedback?

How can we improve it?

Thank you for your feedback!

×