Skip to main content

Worksheet Indexes

Worksheet indexes improve record query and sorting performance. When a worksheet contains a large volume of data (typically tens or hundreds of thousands of records) and query performance begins to degrade, creating indexes can significantly speed up data retrieval.

What Is an Index?​

An index works like the table of contents in a book.

When looking up information in a book, you typically check the table of contents first and then jump directly to the relevant page, rather than reading page by page.

Worksheet indexes follow the same principle. The system builds an ordered structure based on selected fields, allowing it to locate matching records directly during filtering and sorting instead of scanning the entire worksheet. This can significantly improve query performance.

Create an Index​

  1. Open the worksheet editor, then go to More Settings > Search Acceleration and click Create Index.

  2. Select the fields to include in the index.

    A text index cannot include multi-select fields. Each index can contain at most one multi-select field.

  3. Select the index type.

    Unique Index

    A unique index ensures that the combination of values in the indexed fields is unique.

    For example, if you create a unique index on Name + Created Date, the same person can create only one record per day.

  4. Click Save to finish.

Index Limits​

  • Each worksheet can have up to 5 indexes.
  • Each worksheet can have only 1 text index.
  • Each index can contain at most one multi-select field.
  • After creating a unique index:
    • Duplicate values are not allowed.
    • If the indexed fields allow empty values, only one record with empty values can exist in the worksheet.

Edit or Delete an Index​

For existing indexes, you can:

  • Add or remove indexed fields
  • Change the sort order of indexed fields
  • Rename the index
  • Delete the index

The system-generated index_rowid index cannot be renamed.

Q&A​

Why can't I create a unique index?​

Before a unique index can be created, all existing data in the worksheet must already satisfy the uniqueness requirement.

If duplicate values exist, index creation will fail.

The system checks both:

  • Records currently in the worksheet
  • Records in the Recycle Bin

If no duplicates are visible but creation still fails, open the Recycle Bin, click Empty Now, and then try again.

This is because records that have not been permanently deleted after 60 days may no longer appear in the Recycle Bin. In addition, some records removed through batch operations may not be displayed. Permanently emptying the Recycle Bin removes these records and resolves duplicate conflicts.

How can indexes improve query performance?​

Creating appropriate indexes can significantly improve query performance for frequently used search conditions. Before creating indexes, it's helpful to understand a few basic indexing principles.

Which worksheets should have indexes?​

Consider creating indexes when:

  • The worksheet contains tens or hundreds of thousands of records.
  • Filtering, searching, or sorting records has become noticeably slower.
  • Certain query conditions are used frequently.

For smaller worksheets, manually creating indexes is generally unnecessary.

When don't you need indexes?​

Indexes are usually unnecessary when:

  • The worksheet contains relatively few records (for example, fewer than several tens of thousands).
  • Filtering and sorting are rarely performed.
  • Current query performance already meets your needs.

More indexes do not always mean better performance. Indexes provide the greatest benefit only for large datasets with frequent queries.

How do I choose index fields?​

Prioritize fields that are frequently used for:

  • View filters
  • Quick filters
  • Query conditions
  • Sorting
  • Sorting in subforms
  • Sorting related records

In general:

  • Fields with fewer duplicate values make better index candidates.
  • Fields that are frequently queried together should be combined into a composite index.

For example:

  • Product Code is an ideal candidate for an index.
  • A composite index on Last Name + First Name is generally more effective than two separate indexes.

Why does the field order in a composite index matter?​

For composite indexes, the field order and sort direction should match the sorting used in your views whenever possible. Otherwise, the database optimizer may not be able to use the index effectively.

Are more indexes always better?​

No.

Each additional index increases the maintenance overhead whenever records are created, updated, or deleted, which may reduce write performance.

Only create indexes for high-frequency query scenarios where they provide measurable benefits.

Does the system create indexes automatically?​

Yes. In HAP SaaS, the system analyzes daily access logs, identifies frequently executed queries, and automatically creates appropriate indexes.

For this reason, avoid modifying or deleting indexes that were not created manually.

Automatic index creation is not currently supported in HAP Server.

When does a composite index take effect?​

Composite indexes follow the leftmost prefix rule.

For example, if you create the following index:

Name → Graduation Date → Major

The index can be used for these queries:

  • Name
  • Name + Graduation Date
  • Name + Graduation Date + Major

The following queries cannot use this index:

  • Graduation Date
  • Major
  • Graduation Date + Major

Common Scenarios Where Indexes Are Less Effective​

Recommended usage​

For worksheets containing 100,000 or more records, create indexes on fields used in frequent batch queries before running them.

Best query patterns​

  • For text fields, exact-match (Equals) queries generally deliver the best performance, especially when the field has low duplication and is indexed.

  • For multi-select fields such as Member, Department, Organization Role, Multi-select, Relationship field, and other array-based fields, using Is One Of typically provides the best performance.

Query patterns to use with caution​

Even when indexes exist, the following query patterns may not benefit from them:

  • Negative conditions

    Not Equals, Does Not Contain, Does Not Start With, Does Not End With

  • Fuzzy matching and empty-value checks

    Contains, Is Empty, Starts With, Ends With

  • OR conditions

    Filter conditions connected with OR

  • Special fields and sorting

    • Option fields where a single value accounts for a very high percentage of records (for example, more than 30%) have low index selectivity.

    • When Sort by Pinyin is enabled for text fields, sort indexes are generally not used.

Optimization recommendations​

For worksheets with very large datasets, minimize the use of the query patterns above whenever possible to reduce database load and improve query response times.

Whenever possible, rewrite conditions as exact-match queries and ensure that the relevant fields are properly indexed.

Was this document helpful?