AutoIndex Gotcha - Access 2007/2010

<< Click to Display Table of Contents >>

Navigation:  Tables >

AutoIndex Gotcha - Access 2007/2010

Garry Robinson


The AutoIndex option, which is turned on from the Get Go in your databases, will automatically add Indexes to your tables without you knowing it is happening. This little "nuisance" is located in Options (see Figure 1) and then in the Database Designers Tab (Figure 2). My guess is that 80% or more developers are unaware of this option and even if they are, they forget to turn it off in new databases. The result is that there could be many tables in the database with indexes that weren't planned for.  This costs you in speed to write records and costs space to store the unwanted index. In many cases like in Figure 3 which just so happens to come from a Microsoft template, you will even have 2 indexes that do exactly the same thing.

So follow the first 4 pictures and you will understand what I am on about.  For more detail discussions on this topic (Access 2003) and code samples, view this page. For details on how to change indexes, read this

201203_gr_index1_zoom40               201203_gr_index2
Figure 1 - Options        Figure 2 - The not so great AutoIndex option



Figure 3


Figure 4  The Microsoft 2010 Asset Tracking template database has two keys that are identical



Just to Test This Yourself


If you think that this is unlikely, try this little exercise:

1. Make sure that the Object Designer option "Auto Index on Import/Create" (figure 2) has the value "ID" in the list.

2. Open a new table in design mode and add a field with any name.

3. Add "ID" to the end of the field name.

4. Save the table.

5. When prompted, choose Yes to create a primary key.

6. Now open the table in design view and choose View | Indexes from the menu.


You May As Well Clear the Field


In my view, each field index should be added with some thought. Allowing software to add indexes because of an arbitrary list of prefixes/suffixes is just a bit lazy.  If its a small database you will never notice, when tables get to 10 megabytes or more, indexes have an effect on your database.


Figure 5  - Clear the AutoIndex field


This Tip Is Expanded on these Pages

Don't Index Fields with Lots of Identical Data
Don't apply indexes to fields that contain much the same data. For example, indexing a Yes/No field is almost always a performance degrading operation. Similarly, if you have a number field that only contains two or three values, and index wouldn't be a good idea. To check the number of unique entries in an index, use the Access DistinctCount property. Compare this value to the number of records in the table and you can quickly see if the index is doing you any good.

Indexed Fields Should Generally Be For A Physical Purpose

Until such times as you are having performance problems, only index those fields where you are joining tables together in queries or where you have to avoid duplicates in a field or group of fields. Indexes take up space in your database and whenever you save a record, you must also save the index.

Other Somewhat Related Articles
Exportiing all tables to text
Access Subquery Techniques
Simplifying Queries
Supporting Custom Sort Orders