Skip to main content

Worksheet Index

What is an index​

When looking up content in a book, the fastest method is using the table of contents. This works because the contents page sorts chapter titles with their corresponding page numbers - we simply locate the title and page number to quickly find the target content. This is a classic example of indexing.

Similarly, in worksheets, we can create a storage structure by sorting one or multiple fields in a particular order. When querying column data, this sorted structure allows rapid location of corresponding row records, significantly improving search speed. This mechanism is called indexing.

Create Index​

1. Navigate to the worksheet editing page​

Go to worksheet editing page, then [Index Acceleration] > [Create Index].

2. Select index fields​

When creating a "text index", it cannot include single-select/multi-select fields.

Each index can contain only one single-select or multi-select field.

3. Choose index type​

  • Unique index

    If checked, duplicate values in the indexed fields will not be allowed.

    Example: If you create an index with the Name and Creation Date fields, the combination of these two fields must be unique. This ensures that a person can create only one record per day.

  • Full-text index for all text Ffields

    Creates an index for all selected text fields.

    Each worksheet can have only one full-text index for text fields.

4. Click "Create Index" to complete the creation​

Tips:

  • Maximum of 5 indexes can be created per worksheet.

  • Only one text index OR one full-text index for text fields is allowed per worksheet.

  • Text indexes cannot include multi-select fields.

  • Each index can contain at most one multi-select field.

  • Field values in a unique index must be unique (no duplicates allowed). If there are non-required fields, only one empty (null) value is permitted across the entire worksheet, which ensures data uniqueness.

Delete/Edit Index​

  • Add or remove fields from an index.

  • Adjust fields sorting order within the index.

  • Rename the index.

    The system-default index_rowid cannot be renamed.

  • Delete the index.

FAQs​

1. Unique index creation failed?​

When creating a unique index, you must ensure there are no duplicate field values in existing worksheet records. If duplicate values exist, the creation will fail.

Neither active worksheet records nor records in the recycle bin can contain duplicate values. If no duplicates are found but creation still fails, you can empty the recycle bin and try creating again.

This occurs because:

  • Records deleted over 60 days ago won't appear in the recycle bin

  • Some batch operations may not display records in the recycle bin

These records may contain duplicate field values, requiring manual emptying of the recycle bin.

Creating appropriate indexes can significantly improve record retrieval efficiency under specific query conditions. Before creating an index, you need to understand some fundamental principles of index creation.

3. Which worksheets need indexes?​

If a worksheet contains a relatively large amount of data (tens of thousands or even hundreds of thousands of records) and obvious loading delays already occur when querying the worksheet, then you can create indexes in that worksheet to try to speed up retrieval.

4. How to choose fields when creating indexes?​

Key considerations when selecting index fields:

  • Fields with fewer duplicate values in records are more suitable for indexing.

    Example: A unique "Product ID" field is highly appropriate for indexing.

    Example: A "Gender" field (with many duplicate values) is not suitable for indexing.

  • When combinations of fields yield fewer duplicates (e.g. "First Name" and "Last Name"):

    Creating a composite index with these fields is more effective than creating separate indexes for each field.

5. What is the purpose of index field ordering?​

The ordering of index fields relates to the sorting rules when these fields are used in views. When an index contains only a single field, the sorting applied in views will not affect search acceleration. However, for all other cases, you should ensure the field ordering in views matches the ordering configured in the index - otherwise, search acceleration may not be achieved.

6. Are more indexes better?​

Not necessarily. While indexes accelerate data retrieval, the system requires additional time to update indexes during record insertion, updates, and deletion operations. Excessive indexes can significantly slow down these operations and may even negatively impact query performance.

7. Does the system create indexes automatically?​

Yes. The system automatically analyzes frequently used query patterns from daily access logs on public cloud platform and intelligently creates appropriate indexes. Therefore, please do not modify or delete indexes not created by yourself. Note: Automatic index creation is currently not supported in HAP Server edition.