AutoIndex Gotcha - Access 2007/2010

Navigation:  2012 and 13 >

AutoIndex Gotcha - Access 2007/2010

Previous pageReturn to chapter overviewNext page

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

 

201203_gr_index3

Figure 3

 

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

201203_gr_index5

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

 

 

The best way to resolve errors is to use Total Access Analyser from FMS.this is particularly useful when you takeover a database as you can save the list of errors and suggestions in a table/report and prove to people that those issues existed before you took over the database. Click on this button to find out about this product.
 
ad5468x60