Microsoft Office Tutorials and References
In Depth Information
Indexing for Faster Sorts, Searches, and Queries
Indexing for Faster Sorts, Searches, and Queries
Indexing means to instruct Access to keep information about the data in a
field or combination of fields. Because Access keeps this information on
hand, it doesn’t have to actually search through every record in a
database table to sort data, search for data, or run a query. In a large database
table, indexes make sorting, searching, and querying go considerably faster
because Access looks through its own data rather than the data in tables.
The performance difference between querying a database table that has and
has not been indexed is astonishing. That’s the good news. The bad news is
that indexes inflate the size of Access files.
By default, the field you choose as the primary key field is indexed. I
recommend choosing other fields for indexing if you often conduct queries and
searches. When you choose a field to index, choose one with data that varies
from record to record and is likely to be the subject of searches, sorts, and
queries. That way, the index means something. However, a field with data
that is mostly the same from record to record is a waste of a good index,
not to mention disk space. By the way, Access automatically indexes fields
whose names include the words ID, Code, Num, and Key , the idea being that
these fields are likely to store essential information worthy of indexing.
Indexing a field
To index a field, switch to Design view, select the field you want to index,
and on the General tab of the Field Properties part of the Design window,
open the Indexed drop-down list and choose one of these options:
Yes (Duplicates OK): Indexes the field and allows duplicate values to be
entered in the field.
Yes (No Duplicates): Indexes the field and disallows duplicate values.
If you choose this option, the field works something like a primary key
field in that Access does not permit you to enter the same value in two
different records.
Indexing based on more than one field
An index created on more than one field is called a multifield index. Multifield
indexes make sorting, querying, and searching the database table go faster.
They are especially valuable in sorting operations where records in one field
are usually the same but records in a companion field are different. In a large
database table that stores names and addresses, for example, many names
in the Last Name field are the same, so indexing on the Last Name field isn’t
worthwhile, but indexing the First Name and Last Name fields helps Access
distinguish records from one another.
Search JabSto ::

Custom Search