Database Indexes

Choose the right index

Use a lookup index for a field you regularly filter or sort, such as an order date. Use a unique index when a value must not appear twice, such as a membership number.

A compound index combines fields. For example, a unique combination of event_id and email prevents someone registering twice for the same event while allowing them to register for a different event.

Index one field

1. Open Database and edit the field in your table.

2. Find Field Index.

3. Choose whether values must be unique.

4. For text, choose Ignore letter case if Alice and alice should count as the same value.

5. Select Create Index.

If existing records contain duplicates, resolve them before enabling uniqueness. Twidget does not delete those records for you.

Combine several fields

1. Open the table's options menu and select Table Indexes.

2. Under Create compound index, choose at least two fields.

3. Choose ascending or descending order for each field. Put the fields you usually filter together first.

4. Enable Unique combination if the combined values must be different for every record.

5. Optionally enable Ignore letter case.

6. Select Create index.

You can combine up to eight fields. Choose ordinary single-value fields; lists and objects cannot be used in this editor.

Table Indexes opens its own panel. You can open it from the table's menu on the Database list or from the table page. Use Update Table to change ordinary table settings; Table Triggers is a separate option for automatic event runs.

The panel title identifies the selected table. Existing indexes and the compound-index controls appear together.

This example combines reference and category, with Unique combination enabled.

After creation, both fields appear together in Table Indexes, marked Unique. The arrows show each field's order.

Missing values and duplicates

Records missing every field in an index are excluded. Records missing only some indexed fields, and records with empty/null values, can still count towards uniqueness. Test your intended rules with sample records before relying on an index.

Deleting a field used in a compound index removes the entire index. Review your table indexes after changing its fields.

Remove an index

Use Remove beside the index, or Remove Index in the field editor. Removing an index keeps your records, but removes that lookup or uniqueness rule. The automatic record ID index is protected.

Create indexes for common queries rather than every field. Each additional index adds work when records are saved or changed.