Skip to main content

Index Acceleration

What is an index​

The fastest way to find a chapter of a book is through the book's table of contents. This is because in the table of contents, there is an ordering of the titles and corresponding page numbers for each chapter, so we can quickly find the target by simply finding the title and page number in the table of contents first. This is a common example of an index.

Similarly, in a worksheet, you can create a storage structure for a field or a combination of fields based on some sorting rules. When querying a column of data based on this sort structure, you can quickly locate the corresponding record and speed up the search. This is index.

How to create an index​

1. [Form Settings] > [Search Acceleration]

On the editing page of the worksheet, select [Form Settings] > [Search Acceleration].

2. [Create Index]

3. Select the index field

4. Select the index type

  • Unique index

    If checked, the combined content of the fields in the index cannot be duplicate. For example, two fields, name and creation date, are created as indexes, the combination of these two fields cannot be duplicate. In this way, a user can only create one record a day.

Tips:

  • You can only create up to 5 indexes per worksheet.

  • You can only create one text index or full text field index per worksheet; when you create a text index, it cannot contain multiple-select fields; each index can only contain one multiple-select field.

  • After creating a unique index, the field values cannot be duplicate. If the field is not required, there can only be one empty field in the worksheet to ensure the uniqueness of the data.

  • Full-text retrieval is supported for all text fields. Once a text index is created for a worksheet, you cannot create another index of this type.

Basic Operations​

1. Edit Index​

You can redesign the index.

  • Add or delete fields related to the index

  • Adjust the sorting of the fields

  • Modify the index name

    • For the index_rowid index created by system default, the name cannot be modified.

2. Delete Index​

FAQs​

1. How can I use indexes to speed up retrieval?​

Creating an index for a worksheet is like creating an alphabetically searchable table of contents for a dictionary. Creating suitable indexes can speed up the retrieval of records in specific query conditions. Before creating an index, you need to understand some basic principles of creating an index.

2. Which worksheets need to be indexed?​

If there is a relatively large amount of data in a worksheet (usually tens or hundreds of thousands or more) and there is already a noticeable loading delay when querying the worksheet, it is time to create indexes to try to speed up the retrieval.

3. How to select index fields when creating an index?​

Indexes need to be created only for fields that are retrieved in views, filters, quick filters, or sorted in views, subforms, or associated records. When selecting index fields, keep in mind that the fewer duplicate values in a record, the more suitable the field is for indexing. For example, an "Product Number" field that does not allow duplicates would be ideal for indexing; conversely, a "Gender" field would not be suitable for indexing.

If some fields need to be combined together for retrieval, the duplication rate of the field value is lower when they are combined, such as "First Name" field and "Last Name" field, you can select these two fields to create a composite index, which is more effective than creating separate indexes for these two fields.

4. What is the purpose of sorting index fields?​

When there is only one field in the index, the sorting of the fields in the view will not affect the retrieval speedup; otherwise, try to make sure that the field in the view is sorted in the same way as it is set in the index, otherwise the retrieval may not be speeded up.

5. Are more indexes better?​

Not really. Indexes only speed up retrieval, while when adding, updating, or deleting records, the system spends more time updating the indexes, so too many indexes will slow down these operations and even affect the speed of queries.

6. Will the system automatically create indexes for me?​

Yes. Based on the daily access logs of the public cloud platform, HAP will intelligently analyze the frequently used query statements and automatically create appropriate indexes. Therefore, please do not modify and delete indexes that are not created by you. For now, private deployment does not support automatic index creation.

7. Is a multi-field index valid for a single field?​

If you want to use composite index in querying, it needs to conform to the principle of matching query prefixes. For example, when querying student records, a composite index of three fields is created in the order of "Name", "Graduation Time" and "Major". When you query by "Name+Graduation Time+Major", "Name+Graduation Time", or by "Name", you can get the result quickly through the index (which needs to conform to the rules of the index sorting above). However, when you query by "Graduation Time + Major", "Graduation Time" or "Major", the index is not effective and cannot speed up the search.