Using Indexes with Access Queries

Earlier in this course you learned what an index is and why Access uses them. An index is an internal data structure that Access maintains alongside a table. It stores the values of one or more fields in sorted order, together with pointers back to the corresponding records in the table. When a query needs to find, sort, or match records on an indexed field, Access consults the index rather than scanning every row in the table from top to bottom. On a table with a few hundred rows the difference is barely perceptible. On a table with tens of thousands of rows — common in any real business database — the difference between an indexed query and an unindexed one can be several orders of magnitude.

This lesson focuses on a more specific question than "should I use indexes?" You already know the answer to that is yes. The question here is: given a particular query, which fields are the ones worth indexing to get the greatest performance return? Not every field needs an index, and adding indexes indiscriminately has its own costs. The goal is to index strategically.

When Access processes a SELECT query, its query optimizer examines the query's sort order, join conditions, and criteria, then decides whether any available indexes can be used to satisfy those operations more efficiently than a full table scan. If an index exists on a field that the query is filtering or sorting on, the optimizer can jump directly to the relevant portion of the index, retrieve the matching record pointers, and fetch only those rows. If no index exists, it reads every record in the table and evaluates the condition row by row.

The optimizer is reasonably intelligent — it will choose between an index scan and a full table scan based on estimated row counts and selectivity — but it can only use indexes that exist. Your job as the database designer is to make sure the right indexes are in place on the right fields. The optimizer will figure out when to use them.

Three categories of fields give you the greatest performance return when indexed:

1. Fields used for sorting
   
   Any field that appears in the Sort row of the query design grid — either Ascending or Descending — is a candidate for indexing. When Access sorts a result set on an unindexed field, it must read all matching records into memory and then sort them, which is an expensive operation on large tables. When the sort field is indexed, Access can read the records out of the index in already-sorted order, eliminating the sort step entirely or reducing it significantly.
   
   This benefit applies whether you are sorting on a single field or on a combination of fields. If your query sorts by LastName ascending and then by FirstName ascending within each last name, index both fields individually. Access will use them in combination at execution time.
2. Fields used in joins
   
   A join is the operation that links two tables on a common field — typically a primary key in one table and a foreign key in the other. The query optimizer has to match every value in the join field of one table against values in the join field of the other table. Without an index on either side, that matching is done with a nested loop that compares each row in the first table against each row in the second table — an operation that scales as the product of the two table sizes.
   
   Indexing both sides of the join is the single most impactful optimization you can make to a multi-table query. For example, consider a query that joins the Projects table to the Hours table on the ProjectID field. ProjectID in the Projects table is the primary key, so Access automatically creates an index on it. But ProjectID in the Hours table is a foreign key — Access does not automatically index foreign keys. You need to index it manually. With indexes on both sides, Access can perform a merge join or an index lookup join rather than a nested loop, and the performance difference on a busy Hours table is dramatic.
   
   As a general rule: every foreign key field in every table should have an index. If a field is used as the "many" side of a one-to-many relationship, index it.
3. Fields used in criteria
   
   Any field that appears in the Criteria row of the query design grid is filtering the result set. Without an index, Access evaluates the criterion against every record in the table. With an index, Access can narrow the candidate rows to only those that could plausibly match before fetching the full record data.
   
   This benefit scales with selectivity — how narrowly the criterion filters the data. A criterion on a State field that returns 40% of the records provides less index benefit than a criterion on a CustomerID field that returns a single record. Even so, indexing criteria fields is nearly always worthwhile, because the cost of maintaining an index is small and the worst-case scenario (an index that the optimizer decides not to use) is simply that the query runs at the speed it would have without the index.
   
   In complex queries with multiple conditions joined by AND or OR, index all of the fields involved in the criteria. Access can intersect or union the index results before going to the table, which is faster than reading the full table even when multiple conditions must be evaluated.

A compound index — also called a multi-field index — covers two or more fields in a defined order within a single index structure. Most relational database engines, including SQL Server and MySQL, use compound indexes extensively because their optimizers are sophisticated enough to take advantage of them in a wide range of query shapes.

Access is different. Its query optimizer is simpler, and it consistently performs better with individual single-field indexes than with compound indexes covering the same fields. When you need to sort or filter on LastName and FirstName together, the recommendation for Access is to create a separate index on LastName and a separate index on FirstName, rather than a single compound index on (LastName, FirstName).

You can create a compound index in Access through the Indexes dialog (described below), and there are narrow cases where it makes sense — for example, enforcing a unique constraint across two fields simultaneously. But for query performance, individual indexes are the right tool in Access.

Access automatically creates indexes in several situations, so you do not always have to add them manually:

• The primary key field of every table is automatically indexed with a No Duplicates constraint. This is the index on the "one" side of every one-to-many relationship.
• When you define a relationship between two tables using the Relationships window, Access creates an index on the primary key side if one does not already exist, but it does not automatically index the foreign key side. You have to do that manually.
• Access has an AutoIndex feature that creates indexes automatically on fields whose names begin or end with certain strings — ID, key, code, num by default. If your ProjectID foreign key field is named ProjectID, Access will index it automatically when you create the field. If it is named Project_FK or ProjectNumber, it will not. You can view and modify the AutoIndex list under File → Options → Object Designers → AutoIndex on Import/Create.
• Lookup fields — fields whose Display Control property is set to Combo Box or List Box with a row source from another table — are indexed automatically because they function as implicit foreign keys.

Understanding what Access indexes automatically helps you focus your manual indexing effort on the fields that genuinely need attention.

There are two places in Access where you can create or inspect indexes on a table.

The Indexed Field Property

The fastest way to add a single-field index is through the table's Design View. Open the table in Design View, click the field you want to index, and look at the Field Properties panel in the lower half of the screen. The Indexed property has three settings:

• No — no index on this field (the default for most fields).
• Yes (Duplicates OK) — an index is created, and the field is allowed to contain repeated values. Use this for foreign keys and most criteria or sort fields.
• Yes (No Duplicates) — an index is created, and Access enforces uniqueness on the field. Use this for fields that must be unique across all records, such as an email address or a product code. The primary key uses this setting automatically.

Change the Indexed property to the appropriate setting, save the table, and the index is created. This is the recommended approach for individual field indexes.

The Indexes Dialog

For a complete view of all indexes on a table — including compound indexes and the ability to add, rename, or delete them — open the table in Design View and click Indexes on the Design tab in the Show/Hide group. The Indexes dialog displays a grid with three columns: Index Name, Field Name, and Sort Order. Each row in the grid is one field within one index. A compound index is represented by consecutive rows that share the same Index Name, with no Index Name value in the continuation rows.

The dialog also shows two checkbox properties for each index: Primary (marks the index as the primary key index) and Unique (enforces no-duplicate values). You can add a new index by typing a name in an empty Index Name cell, selecting the field from the Field Name dropdown, and choosing a sort order.

Indexes are not free. Every index you add to a table must be updated by Access every time a record in that table is inserted, updated in the indexed field, or deleted. For a table that is primarily read — queried frequently, modified rarely — this cost is negligible and the query performance benefit is clear. For a table that is written to heavily and queried infrequently, the balance tips the other way.

In practice, the write overhead of an index is imperceptible for normal interactive data entry — adding or editing individual records one at a time. It becomes noticeable during bulk operations: importing thousands of records from a CSV file, running an append query that inserts large numbers of rows, or running an update query that modifies an indexed field across the entire table. If you regularly perform bulk imports into a heavily indexed table, one option is to temporarily remove indexes before the import and recreate them afterward using the Indexes dialog, then let Access rebuild them in a single pass rather than updating them incrementally for each row.

The bottom line: index the fields you sort on, join on, and filter on. Keep individual indexes rather than compound ones. Accept the write overhead as the cost of fast reads, and manage it during bulk operations if it becomes a practical issue.

In the next lesson, you will learn how to identify the three different types of joins available in Access and how they can be used.

Click the Quiz link below to answer a few questions about working with query field properties and optimizing queries with indexes.
Optimizing Queries with Indexes - Quiz