Access Database Table Index
Selectively Indexing Fields For Improved Database Performance
Since data is usually entered into a database in random order, and new records are added to the end of a table; indexes come in very handy to organize the data, and make it faster to sort and find. You can create simple indexes using the field's Indexed property, and set it to either Yes (Duplicates OK) or Yes (No Duplicates). Primary keys are ALWAYS indexed Yes (No Duplicates).
An index is an internal sort order on the data contained in the indexed field. To ensure the best performance of your database, every field that is frequently involved in queries, or is frequently sorted should be indexed. If you search for data that is not indexed, Access must search every record in the database to look for matches. If the performance of your database is slow, check your indexes, and you should see a marked improvement in performance.
Composite or multi-field indexes can include up to ten fields. In Composite indexes Access considers all of the fields included in the index.
Table Index Properties
Primary: Designates the table's Primary Key. More than one field can be included in the primary key; however none of the fields included are allowed to be empty, and all must be unique. Each field in the table should be dependent on the Primary Key.
Unique: As implied the field must be unique within a table. When used in composite keys, the combination of all of the field values must be unique. Ignore Nulls: A null value is a blank or empty field. A field that contains a 0 or if you press the space bar in a field, it is no longer null. By default, the property for Ignore Nulls is No. If Ignore Nulls is set to yes any fields that are null will not be indexed. In the case of a composite index, all of the fields in the composite index would have to be null to be ignored.
The Cost of Indexes: Data retrieval is faster when you use an index, but every time you add or edit data in the table Access updates the indexes within that table. If you have ten indexes in a table, all ten indexes must be updated every time a record is edited, added or deleted. Indexes also increase the size of your database by a small amount.
The Value of Indexes: The value of indexes become more important the larger and more accessed the table is, if the table does not contain much data, adding multiple indexes may unnecessarily bloat the size of the database. If a table is constantly changing, but never searched or sorted, you may choose not to index the table other than the primary key. Selectively indexing tables can add to the performance of your database, so my advice is to only index fields in tables that will be most searched or sorted.