Indexes

Designer > Data > Indexes

An index improves the speed of data retrieval operations on an object. Indexes can be created using one or more columns and can also be used to enforce uniqueness of records.

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 the 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 ten fields in an object, which are combined to create an index key.

Tip: Use a maximum of three or four fields, for greatest efficiency.

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:

• Text Area
• 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 Indexes tab
3. Click the [New 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

   Check this box to ensure that the combination of selected fields is unique for every record in the object.

   Use in Import for Record Merging

   When multiple Unique Key indexes exist, this selection determines which one is used to update records during an Import.
   
   For example, if company_name is a unique key, and you import a record for "ABC Co.", that information in the imported record updates the "ABC Co." record if one exists. Otherwise, a new record is added. You can use that feature to merge information from multiple spreadsheets into one object--as when one spreadsheet has company names and addresses, while another has company names and credit terms.

   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 [Save] to create the index

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]]