Indexing Tables Tips For Microsoft AccessDon'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.
This tip comes to you courtesy of Adam Cogan at SSW who can be found at http://www.ssw.com.au. SSW runs the popular Access
Users Group in Sydney.
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.
Watch Out for Auto Index
If you open up Access and select Tools ... Options
and then select the Tables / Queries tag you will find a field called
Auto Index on Import/Create:
If you have any fields in your database with these
prefixes or suffixes, you will find that you have indexes on the field whether you like it
or not. You may as well clear the "Auto Index on Import/Create" text box
on this tab as this is a very unnecessary feature.
The standard keys featured here are
ID;Key;code;num
So look out for those fields cause your database
will be bigger and slower than you think.
Watch
Out For Replication Indexes
If you happen to come across a
database that has replication indexes and the database no longer
synchronizes with other databases, you will be likely to have fields with
replication indexes that are not needed. These may have the name of GUID.
Before you take any of my advice on
this issues, BACKUP your database. OK
you have done that now you can check if the replication indexes are still in
place. If they are, deleting the index will save you a lot of space in big
tables. This is quite easy to do.
Now another thing to realize about
these replication fields is that they are very large, about 30 characters in
size. Multiple this by 100,000 rows and you have a lot of space when
compared to a long integer field.
The next step in clearing up
replication index fields from a database is deleting the field itself. This
sounds easy but it isn't as you need to transfer the table to another
database using append query. Anyway there is a good solution, use this
tool from trigemal
http://trigeminal.com/lang/1033/utility.asp?ItemID=11#11
FYI: I worked on one database where
we reduced the size of the database by 40% by removing the indexes and the
GUID fields.
These tips was provided to you by Garry
Robinson - Software Consultant
Try out our popular Access shareware Graf-FX @ http://www.gr-fx.com/graf-fx.htm
Published 1999-08
Now click on this button
to
read the next tip