Skip to main content

Search Acceleration

What is an index​

The fastest way to find a section 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 section, and we can quickly find the target content 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 rule. 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. In the edit page of the worksheet, select [Search Acceleration]

2. Click [Create Index]

3. Select the fields that need to be indexed

4. Select the index type

  • Unique index

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

Tips:

  • You can only create a maximum of 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 fields of multiple choices; each index can only contain one field of multiple choices.

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

  • Full-text retrieval is supported for all text fields. You cannot create this type of index after creating a text index for a worksheet.

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 name of the index

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

2. Delete the index​

Q&A​

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

Creating suitable indexes can be very effective in speeding up the retrieval of records under specific query conditions. Before creating an index, you need to be familiar with some basic principles of creating indexes.

2. Which worksheets need indexes?​

If the worksheet has a relatively large amount of data (usually with tens or hundreds of thousands of records or more) and there is already a noticeable load delay when querying the worksheet. In this case, an index can be created on that worksheet to speed up the retrieval.

3. How do I select index fields when creating an index?​

Indexes need to be created only for fields that are retrieved in query scenarios such as views, filters, quick filters, or fields that are sorted in views, sub-tables, or associated records. When selecting index fields, keep in mind the principle that the fewer duplicate values in a record, the more suitable it is to create an index. For example, for a "Product Number" field that does not allow duplicates, it would be ideal for indexing; conversely, a "Gender" field would not be suitable for indexing. If some fields always need to participate in retrieval together, they have a lower duplication rate when combined, such as "First Name" and "Last Name", you can select both fields to create a compound index, which works better than creating indexes for both fields separately.

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

The sorting of index fields has to do with the rules you use in the view for this field to participate in the sorting. When the index has only one field, the sorting in the view will not affect the speedup of retrieval; otherwise, try to make sure that the fields in the view are sorted in the same way as in the index, otherwise retrieval may be affected.

5. Are more indexes better?​

Not really. Indexes will only speed up retrieval, and the system will take more time on updating indexes when adding, updating, or deleting records. Too many indexes will obviously slow down these operations, and even affect the speed of queries.

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

Yes, we will automatically learn and intelligently analyze the frequently used query statements based on the daily access logs of the public cloud platform, and automatically create the appropriate indexes. So, please do not modify or delete indexes that are not created by you. Automatic index creation is not available in private deployment environment for now.

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

If you need a compound index in a query, you need to comply with the principle of query prefix matching. For example, if you create a compound index with three fields in the order of "name", "graduation time" and "major" when searching student's academic records, you can use the index to get the query result quickly if you query "name + graduation time + major", "name + graduation time" or "name" only (it needs to comply with the above rules for index sorting). However, if you query only "graduation time + major", "graduation time" or "major", the index is not effective, and you cannot speed up the retrieval.

Have questions about this article？ Send us feedback