UtterAccess HomeUtterAccess Wiki

Welcome Guest ( Log In | Register )

Custom Search
Edit Discussion
> Indexes    
Indexes

Index

Definition: A hint to the database engine that allows more efficient retrieval of data. Indexes can apply to one or more fields within the table.

Indexes are generally added for one of two reasons: (1) to improve performance, or (2) to provide a unique constraint to the data.

An index has properties that further define the field(s) within the scope of the index. For instance, an index may be defined as unique, which would guarantee unique data in all field combinations of the index within the table.

The database engine leverages these indexes when searching for data and can more easily "jump" to the correct starting position to begin reading in data that qualifies.

For an index used to boost performance, the best results are achieved when the majority of the data in a field throughout the table is unique. For example, indexing a Boolean (or Yes/No) field is rarely advantageous because there are only two possible values. However, a field such as LastName may be a good candidate if frequent queries based on LastName are performed.

Too many indexes in a single table can produce an undesireable effect ... The time it takes to write new incoming data can slow down, because the engine not only has to write the new data, but has to update each of the indexes that are involved. So a delicate balance has to be achieved: the fewest possible indexes to produce the fastest possible retrieval of data.

A good general rule of thumb is to only add an index if performance is poor. If adding the index doesn't improve performance, remove it immediately and reconsider your structure. This may sound like trial and error, but there is a method to the madness. You will end up with only the indexes you need and your database will perform as fast as possible.

And, remember, as your data changes with time, so may the needs of your indexes. So be prepared to re-evaluate their addition or deletion during the lifetime of the database.

Edit Discussion
Custom Search
Thank you for your support!
This page has been accessed 6,828 times.  This page was last modified 03:21, 21 July 2012 by Walter Niesz.   Disclaimers