Create Multiple Search Indexes on a Single Table - Lindorm

When a single wide table needs to serve multiple query patterns — each accessing different column combinations — a single search index cannot efficiently cover all access paths. Multiple search indexes let you define up to five independent indexes on one primary table, each covering a different column set. Lindorm automatically routes each query to the most suitable index.

This feature is in public preview. To enable it, contact Lindorm technical support on DingTalk (group ID: s0s3eg3).

Prerequisites

Before you begin, make sure that:

The LindormTable engine is version 2.8.2.3 or later
The Lindorm LTS engine is version 3.8.12.5 or later

To check or upgrade your engine version, see Minor version update.

Limitations

A single primary table supports a maximum of 5 search indexes.
All search indexes on the same primary table must have unique names. Index names cannot conflict with other index types on the same table.

Create multiple search indexes

The following steps walk through creating a wide table, building multiple search indexes on it, writing data, and querying through the indexes.

Step 1: Create a wide table

create table testTable (pk int, c1 int, c2 varchar, c3 long, primary key(pk));

Step 2: Create multiple search indexes on the table

Index columns and properties can overlap across indexes.

create index idx1 using search on testTable (c1,c2);
create index idx2 using search on testTable (c3);
create index idx3 using search on testTable (c1,c2,c3);

Step 3: Write data

Upsert rows into the wide table. Data is automatically synchronized to every search index.

upsert into testTable(pk,c1,c2,c3) values (1,1,'1',1);
upsert into testTable(pk,c1,c2,c3) values (2,2,'2',2);
upsert into testTable(pk,c1,c2,c3) values (3,3,'3',3);
upsert into testTable(pk,c1,c2,c3) values (4,4,'4',4);

Step 4: Query data

Lindorm automatically selects an appropriate search index based on the query columns. When multiple indexes cover the same query, one of them is selected.

Query all three columns — idx3 is selected because it covers c1, c2, and c3:

select * from testTable where c1>0 and c2>'0' and c3>0;

Query two columns — either idx1 or idx3 is selected, since both cover c1 and c2:

select * from testTable where c1>0 and c2>'0';

Query one column — either idx2 or idx3 is selected, since both cover c3:

select * from testTable where c3>0;

Step 5: Specify an index explicitly (optional)

Use the HINT syntax to force a specific index for a query, overriding automatic selection:

select /*+ _l_force_index_('idx1') */ * from testTable where c1>0 and c2>'0';

Step 6: Manage index status

Use ALTER INDEX to independently control the status of each index:

alter index idx1 on testTable inactive;
alter index idx1 on testTable disabled;

The following table describes each status value and its effect on queries and indexing:

Status	Queryable	Data indexed	Notes
ACTIVE	Yes	Yes	Default operating state.
INACTIVE	No	Yes	Paused for queries, but continues to index writes.
DISABLED	No	No	Stops indexing writes. Cannot be changed to another status — run the `REBUILD` command to re-enable the index.