Indexes

In this post, we introduce the concept of indexes in MS Access.

Open up a table in design view. You should see the Ribbon below. Click on the indexes button.

The dialog box that opens up shows us all the indexes for a table.  In this case there is one index called Primary Key, which is set against the CustomerID field and is sorted in Ascending order.

An index has some extra details below as well.

• Primary – signifies this is a Primary Key.
• Unique – signifies that every single number in this field MUST BE UNIQUE.
• Ignore Nulls – states that this field can NEVER BE NULL.

Before reading on, think for a moment why else an index might be useful, aside from ensuring unique values.

Any ideas?

Indexes and Database Search Efficiency

If you said speed then give yourself 10 house points! Imagine trying to find something in the entire Encyclopaedia Britannica if you didn’t have an index! What if all the subjects were jumbled up? You would have to read all nineteen thousand pages to find something! But if you have a correctly sorted index, it doesn’t matter where the subject is as you can use the index to find it!

That is exactly what indexes do in a relational database.  Indexes are sorted lists of data against particular columns.  In the case of Primary Keys an index is made with all the primary key values currently being used and the location of that record within the database.  Using this technique, it doesn’t matter what order records are in the table, we can use the index to find all the records in the correct order.

Indexes can also be made up of any other field you like and need not be unique.  For example you may want a Customers table to have indexes on a customer name.  By doing this we can decrease the search time when doing a lookup on a customer – if we don’t include an index, Access will have to do the equivalent of searching the Encyclopedia Britannica for a name every time we run a query or use a lookup function!

Related Posts