Creating indexes for optimized performance

[rickowen]

Thanks to Roger Carlson and Allen Browne for their timely and appropriate
replies. I'm currently reading Chapter 15 "Application Optimization" -- a
book excerpt.

In discussing Rushmore optimization, the authors state "If you have queries
that don't include multiple restrictions or that contain restrictions on
columns for which you haven't created indexes, Rushmore won't be used. Thus,
it's important to create indexes on all columns that are used in query
restrictions."

That being said, what is the best practice for indexing columns? Creating
multiple column indexes based on the most likely query scenarios, or just
creating a separate index for each column that will be used in a query
restriction?

Thanks,
Rick.

 

[Allen Browne]

Any column that will be regularly used in criteria or sorting should be
indexed if the data is not constantly changing.

The exception is your foreign key fields where you will create relations
with enforced referential integrity: Access will create hidden indexes on
those to enforce RI.

If the data is doing many more edits/deletes/inserts than searches, the
maintaining the indexes could be disadvantageous.

Where a table has *many* relations to other tables, it is possible to run
out of indexes.

Common wisdom is to avoid indexing fields that have few possible values,
such as a yes/no field. However, you need to test this for yourself. For
example, we put an Inactive field into many tables, and are regularly
pulling out just the active records, so we ran some tests with a typical
data set of 15000 customers, and discovered the index yielded results
several times faster, even though it was a yes/no field.

Rushmore is very good. You can trust it to use the indexes in simple cases.
There are some more involved cases where JET chooses a bad query plan, and
you have to rework your query. Other than the query-plan links I gave you
earlier, you need to run some timing tests using the actual table structure
and record count you anticipate for your app.

 

[peregenem]

Allen Browne wrote:

Just to add that choosing the PRIMARY KEY (PK) of a table wisely may
have a positive effect on performance, notably for when using GROUP BY
or BETWEEN. In Access/Jet, the meaning of PK has nothing to do with
theory or logic: its purpose is to determine the physical order on
disk, a.k.a. clustered index, and the clustered index must surely be
the table's most important index as regards performance.

The exception is your foreign key fields where you will create relations
with enforced referential integrity: Access will create hidden indexes on
those to enforce RI.

What do you mean by 'hidden'? I think you may mean that when a FOREIGN
KEY is created via the Access UI or via DAO, indexes are automatically
created but I am not aware of theses index being hidden. Please
clarify.