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 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.
• 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>

Caution

Creating a new index on existing data

If you create a new unique index on existing data and the data has no duplicate entries of the combination of fields, then it will give message “Index created successfully”

If you create a new unique index on an existing data and the data has duplicate entries of the combination of fields, then an index will be created which acts for only future additions or updates

Adding new record to the object

If there is no existing record with same values for fields selected in the unique index, then the record will be added successfully.

If there is an existing record with same values for fields selected in the unique index, then it will give message “Operation results in unique index violation ”

Updating a record

If there exists another record with values to be updated for fields selected in the unique index of the current record, then it will give message “Operation results in unique index violation ".

If there exists no reord with the updated fields for the unique index fields, then the record is updated successfully.

Deleting a record

The delete operation will be successfull even if there is an index applied.

Restoring a record

If there exists another record with the same values for index fields (that is not part of recycle bin) then it will give a message "Operation results in unique index violation". Otherwise, the operation will be successful.

Adding Index to an Object

The addition of unique index to an object will not check for the uniqueness of existing records.

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