Indexes

Designer > Data > Indexes

Indexes are used to access database information efficiently. Once an index is supplied, the database can go directly to the record and retrieve it, without having to search for it.

About Indexes

Users that have the Customize Objects permission can create and modify Indexes in Objects

In addition to speeding up data retrieval, indexes can be used to:

• Prevent Duplicate Records: When you define a Unique Key, a record that has the same key-field values as an existing record cannot be added to the database.
• Streamline Data Searches: When you create a (non-unique) Index Key for an Object, you can make searches run faster by selecting records with a particular key (or set of keys). That way, you pare down the data set to minimize the number of records that are actually searched.

How It Works

An index key uniquely identifies a record. In effect, it defines an address for that record. Once they system has that address, it can go straight to the record.

If you are looking for a friend in a city, one option is to go door to door looking for that person. That process is analogous to a search. Searches are thorough, and they're great when you want to retrieve a collection of records. But they're not very efficient.

Another option is to go the phone book, and find your friend's address. The phone book is an index. The person's name is the key you use to look up their address. And once you have their address, you can go straight to your friend's house.

Choosing Fields for Indexes

You can select up to three fields in an object, which are combined to create an index key.

Most field types are available for use as components in an index, with the following exceptions - these field types are not eligible as components in an index:

• Multiline text
• Rich text area
• File
• Image
• Custom components

Guidelines

• You can create multiple index keys for an object.
• Any regular Index Key can be selected as a Unique Key.
• The collection of values in an Index Key may contain duplicates. The collection of values in a Unique Key cannot.
• Multiple Unique Keys can be created.
• After an index is created, only the Unique Key checkbox can be modified. The selected fields cannot be changed. (The key cannot be edited. A new index field can be created, instead.)
• The sum of the length (maximum storage length) of the fields selected for the index cannot exceed 1024 bytes.

Choosing fields for index keys takes some consideration.

Index Keys: You should select fields that distinguish the record from others in the database, and choose to combine typical "demographic" fields. For example:

<syntaxhighlight lang="java" enclose="div">

City
State
Industry+Zip Code
Product+State
Opportunity+Close Date
URL

</syntaxhighlight>

Unique Keys: Because the resulting value must be unique, generic "demographic" fields are generally not suitable. The following list includes the kinds of field combinations you should consider for a Unique Index Key:

<syntaxhighlight lang="java" enclose="div">

Email Address
Contact Name+Product+Date Purchased
Account Number

</syntaxhighlight>

Effects of Operations

Creating a new Unique Key index on existing data

• If the data has no duplicate entries for that combination of fields, the index is created successfully.
• If the data has duplicate entries for that combination of fields, index creation fails.
• The check for duplicate entries includes entries in the Recycle Bin, since they could be restored at a later date.

Adding a new record to an object with a Unique Key index

• If no existing record has the same values for the fields that make up the unique index, the record is added successfully.
• If there is an existing record with the same values for those fields, an error occurs.

Updating a record to change Unique Key values

• If no other record exists with those values, the update succeeds.
• If another record exists with those values, the update fails.

Deleting a record

When a record is deleted, corresponding index entries are deleted, as well.

Working with Indexes

Add an Index

1. Click Designer > Data > Objects > {object}
2. Click the Index tab
3. Click the [Add Index] button to add an index, and add this information:

   Name

   Name is a Text field. Name takes only alphanumeric characters, and underscore.

   Enable Unique Key

   Enable unique Key is a check box. Check this box if you need to make the combination of selected fields unique.

   Select Fields to be used in Index

   This contains two combo boxes. Move one or more fields from left combo box to right combo box. The items in the right combo box will be used to create the index.

4. Click the [Save] Button to create the index with given values

Delete an Index

To delete an existing index:

1. Click Designer > Data > Objects > {object}
2. Click the Index tab
3. Click the [Delete] button

[[Category:Template:Features]]