Create and use an index to improve performance

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

What is an index?

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 you choose to index. After Access obtains the location from the index, it can then retrieve the data by moving directly to the correct location. In this way, using an index can be considerably faster than scanning through all of the records to find the data.

Decide which fields to index

You can create indexes that are based on a single field or on multiple fields. You'll probably want to index fields that you search frequently, fields that you sort, and fields that you join to fields in other tables in multiple table queries. Indexes can speed up searches and 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 each 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.

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.

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.

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

To create a multiple-field index for a table, you include a row 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 containing 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.

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. 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 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.

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

7. In the Indexes window, under Index Properties, set 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.

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

9. Close the Indexes window.

Delete an index

If you find that an index becomes unnecessary or is having too great an impact 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 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.

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

5. 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.

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 or press CTRL + S..

5. Close the Indexes window.

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 with 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. Click File > Options. 

2. Click Object Designers and then, under Table design, add, edit, or remove 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.

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 altering 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